CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_STOCK_TRANSACTION_DATA" 
AS
  /***********************************************************************************************************************************
  procedure
  ***********************************************************************************************************************************/
  /* ----------------------------- insert the records of stocks ------------------------------------------------ */
  -- 这个存储过程不再使用了
PROCEDURE WRITE_STOCK_TRANSACTION_DATA(
    p_date                     IN DATE,
    p_code                     IN VARCHAR2,
    p_open_price               IN NUMBER,
    p_close_price              IN NUMBER,
    p_highest_price            IN NUMBER,
    p_lowest_price             IN NUMBER,
    p_change_amount            IN NUMBER,
    p_change_range             IN NUMBER,
    p_turnover_rate            IN NUMBER,
    p_volume                   IN NUMBER,
    p_turnover                 IN NUMBER,
    p_total_market_value       IN NUMBER,
    p_circulation_market_value IN NUMBER)
IS
BEGIN
  INSERT
  INTO STOCK_TRANSACTION_DATA
    (
      date_,
      code_,
      open_price,
      close_price,
      highest_price,
      lowest_price,
      change_amount,
      change_range,
      turnover_rate,
      volume,
      turnover,
      total_market_value,
      circulation_market_value
    )
    VALUES
    (
      p_date,
      p_code,
      p_open_price,
      p_close_price,
      p_highest_price,
      p_lowest_price,
      p_change_amount,
      p_change_range,
      p_turnover_rate,
      p_volume,
      p_turnover,
      p_total_market_value,
      p_circulation_market_value
    );
END WRITE_STOCK_TRANSACTION_DATA;
/*---------------------------------------------------- calculate five moving average -------------------------------------------------*/
PROCEDURE CALCULATE_FIVE
IS
BEGIN
  ------------------------------------------ standard declare section ---------------------------------------------------
  DECLARE
    -- 表示stock_code
    v_code VARCHAR2(10);
    -- 表示5天收盘价的和
    v_five_sum NUMBER := 0;
    -- 表示5天收盘价的平均值
    v_five_average NUMBER := 0;
    -- 定义一个含有5个数值型数据的数组
  type type_array IS varray(5) OF NUMBER;
  array_five type_array := type_array();
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT std.code_ FROM stock_transaction_data std;
  CURSOR cur_all_stock_close_price
  IS
    SELECT std.close_price,
      std.date_
    FROM stock_transaction_data std
    WHERE std.code_ = v_code
    ORDER BY std.date_ ASC;
  ------------------------------------------ standard declare section ---------------------------------------------------
BEGIN
  --------------------------------- standard for loop section --------------------------------------------------------
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
    array_five := type_array();
    FOR j IN cur_all_stock_close_price
    LOOP
      array_five.extend; -- 扩展数组，扩展一个元素
      array_five(array_five.count) := j.close_price;
      IF mod(array_five.count, 5)   = 0 THEN
        -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
        v_five_sum := 0;
        FOR x IN 1 .. array_five.count
        LOOP
          -- 求5天收盘价的和
          v_five_sum := v_five_sum + array_five(x);
        END LOOP;
        -- dbms_output.put_line('v_five_sum'||'  :  '||v_five_sum);
        -- 删除数组中的第一个元素，将其与4个元素向前挪一位，并删除下标为5的元素
        FOR y IN 1 .. array_five.count - 1
        LOOP
          array_five(y) := array_five(y + 1);
        END LOOP;
        array_five.trim;
        -- 5天收盘价的平均值
        v_five_average := v_five_sum / 5;
        -- 向所有记录的FIVE列插入5天收盘价的平均值
        UPDATE stock_transaction_data std
        SET std.ma5     = ROUND(v_five_average, 2)
        WHERE std.code_ = v_code
        AND std.date_   = j.date_;
        -- dbms_output.put_line('v_five_average'||'  :  '||v_five_average);
      END IF;
    END LOOP;
  END LOOP;
  COMMIT;
END;
END CALCULATE_FIVE;
-- end procedure
/*---------------------------------------------------- calculate ten moving average -------------------------------------------------*/
PROCEDURE CALCULATE_TEN
IS
BEGIN
  ------------------------------------------ standard declare section ---------------------------------------------------
  DECLARE
    -- 表示stock_code
    v_code VARCHAR2(10);
    -- 表示10天收盘价的和
    v_ten_sum NUMBER := 0;
    -- 表示10天收盘价的平均值
    v_ten_average NUMBER := 0;
    -- 定义一个含有10个数值型数据的数组
  type type_array IS varray(10) OF NUMBER;
  array_ten type_array := type_array();
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT std.code_ FROM stock_transaction_data std;
  CURSOR cur_all_stock_close_price
  IS
    SELECT std.close_price,
      std.date_
    FROM stock_transaction_data std
    WHERE std.code_ = v_code
    ORDER BY std.date_ ASC;
  ------------------------------------------ standard declare section ---------------------------------------------------
BEGIN
  --------------------------------- standard for loop section --------------------------------------------------------
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
    array_ten := type_array();
    FOR j IN cur_all_stock_close_price
    LOOP
      array_ten.extend; -- 扩展数组，扩展一个元素
      array_ten(array_ten.count) := j.close_price;
      IF mod(array_ten.count, 10) = 0 THEN
        -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
        v_ten_sum := 0;
        FOR x IN 1 .. array_ten.count
        LOOP
          -- 求10天收盘价的和
          v_ten_sum := v_ten_sum + array_ten(x);
        END LOOP;
        -- dbms_output.put_line('ten_sum'||'  :  '||v_ten_sum);
        -- 删除数组中的第一个元素，将其与9个元素向前挪一位，并删除下标为10的元素
        FOR y IN 1 .. array_ten.count - 1
        LOOP
          array_ten(y) := array_ten(y + 1);
        END LOOP;
        array_ten.trim;
        -- 10天收盘价的平均值
        v_ten_average := v_ten_sum / 10;
        -- 向所有记录的FIVE列插入10天收盘价的平均值
        UPDATE stock_transaction_data std
        SET std.ma10    = ROUND(v_ten_average, 2)
        WHERE std.code_ = v_code
        AND std.date_   = j.date_;
        -- dbms_output.put_line('v_ten_average'||'  :  '||v_ten_average);
      END IF;
    END LOOP;
  END LOOP;
  COMMIT;
END;
END CALCULATE_TEN;
-- end procedure
/*---------------------------------------------------- calculate twenty moving average -------------------------------------------------*/
PROCEDURE CALCULATE_TWENTY
IS
BEGIN
  ------------------------------------------ standard declare section ---------------------------------------------------
  DECLARE
    -- 表示stock_code
    v_code VARCHAR2(10);
    -- 表示20天收盘价的和
    v_twenty_sum NUMBER := 0;
    -- 表示20天收盘价的平均值
    v_twenty_average NUMBER := 0;
    -- 定义一个含有20个数值型数据的数组
  type type_array IS varray(20) OF NUMBER;
  array_twenty type_array := type_array();
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT std.code_ FROM stock_transaction_data std;
  CURSOR cur_al_sStock_close_price
  IS
    SELECT std.close_price,
      std.date_
    FROM stock_transaction_data std
    WHERE std.code_ = v_code
    ORDER BY std.date_ ASC;
  ------------------------------------------ standard declare section ---------------------------------------------------
BEGIN
  --------------------------------- standard for loop section --------------------------------------------------------
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
    array_twenty := type_array();
    FOR j IN cur_al_sStock_close_price
    LOOP
      array_twenty.extend; -- 扩展数组，扩展一个元素
      array_twenty(array_twenty.count) := j.close_price;
      IF mod(array_twenty.count, 20)    = 0 THEN
        -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
        v_twenty_sum := 0;
        FOR x IN 1 .. array_twenty.count
        LOOP
          -- 求20天收盘价的和
          v_twenty_sum := v_twenty_sum + array_twenty(x);
        END LOOP;
        -- dbms_output.put_line('v_twenty_sum'||'  :  '||v_twenty_sum);
        -- 删除数组中的第一个元素，将其与19个元素向前挪一位，并删除下标为20的元素
        FOR y IN 1 .. array_twenty.count - 1
        LOOP
          array_twenty(y) := array_twenty(y + 1);
        END LOOP;
        array_twenty.trim;
        -- 20天收盘价的平均值
        v_twenty_average := v_twenty_sum / 20;
        -- 向所有记录的FIVE列插入20天收盘价的平均值
        UPDATE stock_transaction_data std
        SET std.ma20    = ROUND(v_twenty_average, 2)
        WHERE std.code_ = v_code
        AND std.date_   = j.date_;
        -- dbms_output.put_line('v_twenty_average'||'  :  '||v_twenty_average);
      END IF;
    END LOOP;
  END LOOP;
  COMMIT;
END;
END CALCULATE_TWENTY;
-- end procedure
/*---------------------------------------------------- calculate sixty moving average -------------------------------------------------*/
PROCEDURE CALCULATE_SIXTY
IS
BEGIN
  ------------------------------------------ standard declare section ---------------------------------------------------
  DECLARE
    -- 表示stock_code
    v_code VARCHAR2(10);
    -- 表示60天收盘价的和
    v_sixty_sum NUMBER := 0;
    -- 表示60天收盘价的平均值
    v_sixty_average NUMBER := 0;
    -- 定义一个含有60个数值型数据的数组
  type type_array IS varray(60) OF NUMBER;
  array_sixty type_array := type_array();
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT std.code_ FROM stock_transaction_data std;
  CURSOR cur_all_stock_close_price
  IS
    SELECT std.close_price,
      std.date_
    FROM stock_transaction_data std
    WHERE std.code_ = v_code
    ORDER BY std.date_ ASC;
  ------------------------------------------ standard declare section ---------------------------------------------------
BEGIN
  --------------------------------- standard for loop section --------------------------------------------------------
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
    array_sixty := type_array();
    FOR j IN cur_all_stock_close_price
    LOOP
      array_sixty.extend; -- 扩展数组，扩展一个元素
      array_sixty(array_sixty.count) := j.close_price;
      IF mod(array_sixty.count, 60)   = 0 THEN
        -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
        v_sixty_sum := 0;
        FOR x IN 1 .. array_sixty.count
        LOOP
          -- 求60天收盘价的和
          v_sixty_sum := v_sixty_sum + array_sixty(x);
        END LOOP;
        -- dbms_output.put_line('v_sixty_sum'||'  :  '||v_sixty_sum);
        -- 删除数组中的第一个元素，将其与59个元素向前挪一位，并删除下标为60的元素
        FOR y IN 1 .. array_sixty.count - 1
        LOOP
          array_sixty(y) := array_sixty(y + 1);
        END LOOP;
        array_sixty.trim;
        -- 60天收盘价的平均值
        v_sixty_average := v_sixty_sum / 60;
        -- 向所有记录的FIVE列插入60天收盘价的平均值
        UPDATE stock_transaction_data std
        SET std.ma60    = ROUND(v_sixty_average, 2)
        WHERE std.code_ = v_code
        AND std.date_   = j.date_;
        -- dbms_output.put_line('v_sixty_average'||'  :  '||v_sixty_average);
      END IF;
    END LOOP;
  END LOOP;
  COMMIT;
END;
END CALCULATE_SIXTY;
-- end procedure
/*---------------------------------------------------- calculate one hundred twenty moving average -------------------------------------------------*/
PROCEDURE CALCULATE_ONEHUNDREDTWENTY
IS
BEGIN
  ------------------------------------------ standard declare section ---------------------------------------------------
  DECLARE
    -- 表示stock_code
    v_code VARCHAR2(10);
    -- 表示120天收盘价的和
    v_one_hundred_twenty_sum NUMBER := 0;
    -- 表示120天收盘价的平均值
    v_one_hundred_twenty_average NUMBER := 0;
    -- 定义一个含有120个数值型数据的数组
  type type_array IS varray(120) OF NUMBER;
  array_one_hundred_twenty type_array := type_array();
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT std.code_ FROM stock_transaction_data std;
  CURSOR cur_all_stock_close_price
  IS
    SELECT std.close_price,
      std.date_
    FROM stock_transaction_data std
    WHERE std.code_ = v_code
    ORDER BY std.date_ ASC;
  ------------------------------------------ standard declare section ---------------------------------------------------
BEGIN
  --------------------------------- standard for loop section --------------------------------------------------------
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
    array_one_hundred_twenty := type_array();
    FOR j IN cur_all_stock_close_price
    LOOP
      array_one_hundred_twenty.extend; -- 扩展数组，扩展一个元素
      array_one_hundred_twenty(array_one_hundred_twenty.count) := j.close_price;
      IF mod(array_one_hundred_twenty.count, 120)               = 0 THEN
        -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
        v_one_hundred_twenty_sum := 0;
        FOR x IN 1 .. array_one_hundred_twenty.count
        LOOP
          -- 求120天收盘价的和
          v_one_hundred_twenty_sum := v_one_hundred_twenty_sum + array_one_hundred_twenty(x);
        END LOOP;
        -- dbms_output.put_line('v_one_hundred_twenty_sum'||'  :  '||v_one_hundred_twenty_sum);
        -- 删除数组中的第一个元素，将其与119个元素向前挪一位，并删除下标为120的元素
        FOR y IN 1 .. array_one_hundred_twenty.count - 1
        LOOP
          array_one_hundred_twenty(y) := array_one_hundred_twenty(y + 1);
        END LOOP;
        array_one_hundred_twenty.trim;
        -- 120天收盘价的平均值
        v_one_hundred_twenty_average := v_one_hundred_twenty_sum / 120;
        -- 向所有记录的FIVE列插入120天收盘价的平均值
        UPDATE stock_transaction_data std
        SET std.ma120   = ROUND(v_one_hundred_twenty_average, 2)
        WHERE std.code_ = v_code
        AND std.date_   = j.date_;
        -- dbms_output.put_line('v_one_hundred_twenty_average'||'  :  '||v_one_hundred_twenty_average);
      END IF;
    END LOOP;
  END LOOP;
  COMMIT;
END;
END CALCULATE_ONEHUNDREDTWENTY;
-- end procedure
/*---------------------------------------------------- calculate one hundred fifty moving average -------------------------------------------------*/
PROCEDURE CALCULATE_TWOHUNDREDFIFTY
IS
BEGIN
  ------------------------------------------ standard declare section ---------------------------------------------------
  DECLARE
    -- 表示stock_code
    v_code VARCHAR2(10);
    -- 表示250天收盘价的和
    v_one_hundred_fifty_sum NUMBER := 0;
    -- 表示250天收盘价的平均值
    v_one_hundred_fifty_average NUMBER := 0;
    -- 定义一个含有250个数值型数据的数组
  type type_array IS varray(250) OF NUMBER;
  array_one_hundred_fifty type_array := type_array();
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT std.code_ FROM stock_transaction_data std;
  CURSOR cur_all_stock_close_price
  IS
    SELECT std.close_price,
      std.date_
    FROM stock_transaction_data std
    WHERE std.code_ = v_code
    ORDER BY std.date_ ASC;
  ------------------------------------------ standard declare section ---------------------------------------------------
BEGIN
  --------------------------------- standard for loop section --------------------------------------------------------
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
    array_one_hundred_fifty := type_array();
    FOR j IN cur_all_stock_close_price
    LOOP
      array_one_hundred_fifty.extend; -- 扩展数组，扩展一个元素
      array_one_hundred_fifty(array_one_hundred_fifty.count) := j.close_price;
      IF mod(array_one_hundred_fifty.count, 250)              = 0 THEN
        -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
        v_one_hundred_fifty_sum := 0;
        FOR x IN 1 .. array_one_hundred_fifty.count
        LOOP
          -- 求250天收盘价的和
          v_one_hundred_fifty_sum := v_one_hundred_fifty_sum + array_one_hundred_fifty(x);
        END LOOP;
        -- dbms_output.put_line('v_one_hundred_fifty_sum'||'  :  '||v_one_hundred_fifty_sum);
        -- 删除数组中的第一个元素，将其与249个元素向前挪一位，并删除下标为250的元素
        FOR y IN 1 .. array_one_hundred_fifty.count - 1
        LOOP
          array_one_hundred_fifty(y) := array_one_hundred_fifty(y + 1);
        END LOOP;
        array_one_hundred_fifty.trim;
        -- 250天收盘价的平均值
        v_one_hundred_fifty_average := v_one_hundred_fifty_sum / 250;
        -- 向所有记录的FIVE列插入250天收盘价的平均值
        UPDATE stock_transaction_data std
        SET std.ma250   = ROUND(v_one_hundred_fifty_average, 2)
        WHERE std.code_ = v_code
        AND std.date_   = j.date_;
        -- dbms_output.put_line('v_one_hundred_fifty_average'||'  :  '||v_one_hundred_fifty_average);
      END IF;
    END LOOP;
  END LOOP;
  COMMIT;
END;
END CALCULATE_TWOHUNDREDFIFTY;
-- end procedure
/*---------------------------------------------- calculate infinite moving average -----------------------------------------
procedure CALCULATE_INFINITE is
begin
------------------------------------------ standard declare section ---------------------------------------------------
declare
-- 表示stock_code
stockCode varchar2(10);
-- 表示所有交易日收盘价的和
infiniteSum number:=0;
-- 表示所有交易日收盘价的平均值
infiniteAverage number:=0;
-- 定义一个含有无限个数值型数据的数组
type type_array is table of number;
arrayInfinite type_array:=type_array();
-- define cursor section.返回全部stock_code
cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
cursor allStockClose is select t.stock_close,t.stock_date from stock_moving_average t where t.stock_code=stockCode order by t.stock_date asc;
------------------------------------------ standard declare section ---------------------------------------------------
begin
--------------------------------- standard for loop section --------------------------------------------------------
for i in allStockCode loop
stockCode:=i.stock_code;
dbms_output.put_line('**************   stockCode'||'  :  '||stockCode);
arrayInfinite:=type_array();
infiniteSum:=0;
infiniteAverage:=0;
for j in allStockClose loop
arrayInfinite.extend;-- 扩展数组，扩展一个元素
arrayInfinite(arrayInfinite.count):=j.stock_close;
-- 求所有天收盘价的和
infiniteSum:=infiniteSum+arrayInfinite(arrayInfinite.count);
dbms_output.put_line('infiniteSum'||'  :  '||infiniteSum);
-- 所有天收盘价的平均值
infiniteAverage:=infiniteSum/arrayInfinite.count;
-- 向所有记录的FIVE列插入120天收盘价的平均值
update stock_moving_average t set t.infinite=round(infiniteAverage,2) where t.stock_code=stockCode and t.stock_date=j.stock_date;
dbms_output.put_line('infiniteAverage'||'  :  '||infiniteAverage);
end loop;
end loop;
end;
end CALCULATE_INFINITE;
-- end procedure
*/
/*---------------------------------------------------- write moving average by date -------------------------------------------------*/
PROCEDURE WRITE_MOVING_AVERAGE_BY_DATE(
    p_date IN VARCHAR2)
IS
  -- 表示stock_code
  v_code VARCHAR2(10);
  -- 表示5天收盘价的平均值
  v_five_average NUMBER := 0;
  -- 表示10天收盘价的平均值
  v_ten_average NUMBER := 0;
  -- 表示20天收盘价的平均值
  v_twenty_average NUMBER := 0;
  -- 表示60天收盘价的平均值
  v_sixty_average NUMBER := 0;
  -- 表示120天收盘价的平均值
  v_one_hundred_twenty_average NUMBER := 0;
  -- 表示250天收盘价的平均值
  v_two_hundred_fifty_average NUMBER := 0;
  -- 表示所有天收盘价的平均值
  --infiniteAverage number:=0;
  -- 用于判断某只股票的记录数是否可以计算均线
  v_average_num NUMBER;
  -- 计算某只股票，在某个交易日之前的所有交易记录
  CURSOR cur_stock_row_num_by_date
  IS
    SELECT t.code_,
      COUNT(*) row_num
    FROM stock_transaction_data t
    WHERE t.date_ <= to_date(p_date, 'yyyy-mm-dd')
    GROUP BY t.code_;
BEGIN
  FOR i IN cur_stock_row_num_by_date
  LOOP
    v_code        := i.code_;
    v_average_num := i.row_num;
    /*select
    (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=5 order by t.stock_date desc)),
    (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=10 order by t.stock_date desc)),
    (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=20 order by t.stock_date desc)),
    (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=60 order by t.stock_date desc)),
    (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=120 order by t.stock_date desc)),
    (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=250 order by t.stock_date desc))
    into fiveAverage,tenAverage,twentyAverage,sixtyAverage,oneHundredTwentyAverage,twoHundredFiftyAverage
    from dual;
    update stock_moving_average t
    set t.five=round(fiveAverage,2),t.ten=round(tenAverage,2),t.twenty=round(twentyAverage,2),
    t.sixty=round(sixtyAverage,2),t.one_hundred_twenty=round(oneHundredTwentyAverage,2),
    t.two_hundred_fifty=round(twoHundredFiftyAverage,2)
    where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
    commit;*/
    -- 每只股票都重置如下变量
    v_five_average               := NULL;
    v_ten_average                := NULL;
    v_twenty_average             := NULL;
    v_sixty_average              := NULL;
    v_one_hundred_twenty_average := NULL;
    v_two_hundred_fifty_average  := NULL;
    -- 更新所有股票某一天的5日均线
    IF v_average_num >= 5 THEN
      SELECT AVG(d.close_price)
      INTO v_five_average
      FROM
        (SELECT *
        FROM stock_transaction_data t
        WHERE t.code_ = v_code
        AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
        ORDER BY t.date_ DESC
        ) d
      WHERE rownum <= 5;
    END IF;
    -- 更新所有股票某一天的10日均线
    IF v_average_num >= 10 THEN
      SELECT AVG(d.close_price)
      INTO v_ten_average
      FROM
        (SELECT *
        FROM stock_transaction_data t
        WHERE t.code_ = v_code
        AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
        ORDER BY t.date_ DESC
        ) d
      WHERE rownum <= 10;
    END IF;
    -- 更新所有股票某一天的20日均线
    IF v_average_num >= 20 THEN
      SELECT AVG(d.close_price)
      INTO v_twenty_average
      FROM
        (SELECT *
        FROM stock_transaction_data t
        WHERE t.code_ = v_code
        AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
        ORDER BY t.date_ DESC
        ) d
      WHERE rownum <= 20;
    END IF;
    -- 更新所有股票某一天的60日均线
    IF v_average_num >= 60 THEN
      SELECT AVG(d.close_price)
      INTO v_sixty_average
      FROM
        (SELECT *
        FROM stock_transaction_data t
        WHERE t.code_ = v_code
        AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
        ORDER BY t.date_ DESC
        ) d
      WHERE rownum <= 60;
    END IF;
    -- 更新所有股票某一天的120日均线
    IF v_average_num >= 120 THEN
      SELECT AVG(d.close_price)
      INTO v_one_hundred_twenty_average
      FROM
        (SELECT *
        FROM stock_transaction_data t
        WHERE t.code_ = v_code
        AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
        ORDER BY t.date_ DESC
        ) d
      WHERE rownum <= 120;
    END IF;
    -- 更新所有股票某一天的250日均线
    IF v_average_num >= 250 THEN
      SELECT AVG(d.close_price)
      INTO v_two_hundred_fifty_average
      FROM
        (SELECT *
        FROM stock_transaction_data t
        WHERE t.code_ = v_code
        AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
        ORDER BY t.date_ DESC
        ) d
      WHERE rownum <= 250;
    END IF;
    UPDATE stock_transaction_data t
    SET t.ma5     = nvl2(v_five_average, ROUND(v_five_average, 2), NULL),
      t.ma10      = nvl2(v_ten_average, ROUND(v_ten_average, 2), NULL),
      t.ma20      = nvl2(v_twenty_average, ROUND(v_twenty_average, 2), NULL),
      t.ma60      = nvl2(v_sixty_average, ROUND(v_sixty_average, 2), NULL),
      t.ma120     = nvl2(v_one_hundred_twenty_average, ROUND(v_one_hundred_twenty_average, 2), NULL),
      t.ma250     = nvl2(v_two_hundred_fifty_average, ROUND(v_two_hundred_fifty_average, 2), NULL)
    WHERE t.code_ = v_code
    AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
    -- 更新所有股票某一天的所有日均线
    /*select avg(d.stock_close) into infiniteAverage from(
    select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') order by t.stock_date desc
    ) d;
    update stock_moving_average t set t.infinite=round(infiniteAverage,2) where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
    */
  END LOOP;
  COMMIT;
END WRITE_MOVING_AVERAGE_BY_DATE;
/*procedure WRITE_MOVING_AVERAGE_BY_DATE(p_date in varchar2) is
begin
------------------------------------------ standard declare section ---------------------------------------------------
declare
-- 表示stock_code
v_code varchar2(10);
-- 表示5天收盘价的平均值
v_five_average number:=0;
-- 表示10天收盘价的平均值
v_ten_average number:=0;
-- 表示20天收盘价的平均值
v_twenty_average number:=0;
-- 表示60天收盘价的平均值
v_sixty_average number:=0;
-- 表示120天收盘价的平均值
v_one_hundred_twenty_average number:=0;
-- 表示250天收盘价的平均值
v_two_hundred_fifty_average number:=0;
-- 表示所有天收盘价的平均值
--infiniteAverage number:=0;
-- define cursor section.返回全部code_字段
cursor cur_all_code is select distinct std.code_ from stock_transaction_data std;
------------------------------------------ standard declare section ---------------------------------------------------
begin
--------------------------------- standard for loop section --------------------------------------------------------
for i in cur_all_code loop
v_code:=i.code_;
-- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
-- 更新所有股票某一天的5日均线
select avg(d.close_price) into v_five_average from(
select * from(
select * from stock_transaction_data std
where std.code_=v_code and std.date_<=to_date(p_date,'yyyy-mm-dd') order by std.date_ desc
) where rownum<=5
) d;
update stock_transaction_data std set std.ma5=round(v_five_average,2) where std.code_=v_code and std.date_=to_date(p_date,'yyyy-mm-dd');
-- 更新所有股票某一天的10日均线
select avg(d.close_price) into v_ten_average from(
select * from(
select * from stock_transaction_data std
where std.code_=v_code and std.date_<=to_date(p_date,'yyyy-mm-dd') order by std.date_ desc
) where rownum<=10
) d;
update stock_transaction_data std set std.ma10=round(v_ten_average,2) where std.code_=v_code and std.date_=to_date(p_date,'yyyy-mm-dd');
-- 更新所有股票某一天的20日均线
select avg(d.close_price) into v_twenty_average from(
select * from(
select * from stock_transaction_data std
where std.code_=v_code and std.date_<=to_date(p_date,'yyyy-mm-dd') order by std.date_ desc
) where rownum<=20
) d;
update stock_transaction_data std set std.ma20=round(v_twenty_average,2) where std.code_=v_code and std.date_=to_date(p_date,'yyyy-mm-dd');
-- 更新所有股票某一天的60日均线
select avg(d.close_price) into v_sixty_average from(
select * from(
select * from stock_transaction_data std
where std.code_=v_code and std.date_<=to_date(p_date,'yyyy-mm-dd') order by std.date_ desc
) where rownum<=60
) d;
update stock_transaction_data std set std.ma60=round(v_sixty_average,2) where std.code_=v_code and std.date_=to_date(p_date,'yyyy-mm-dd');
-- 更新所有股票某一天的120日均线
select avg(d.close_price) into v_one_hundred_twenty_average from(
select * from(
select * from stock_transaction_data std
where std.code_=v_code and std.date_<=to_date(p_date,'yyyy-mm-dd') order by std.date_ desc
) where rownum<=120
) d;
update stock_transaction_data std set std.ma120=round(v_one_hundred_twenty_average,2) where std.code_=v_code and std.date_=to_date(p_date,'yyyy-mm-dd');
-- 更新所有股票某一天的250日均线
select avg(d.close_price) into v_two_hundred_fifty_average from(
select * from(
select * from stock_transaction_data std
where std.code_=v_code and std.date_<=to_date(p_date,'yyyy-mm-dd') order by std.date_ desc
) where rownum<=250
) d;
update stock_transaction_data std set std.ma250=round(v_two_hundred_fifty_average,2) where std.code_=v_code and std.date_=to_date(p_date,'yyyy-mm-dd');
-- 更新所有股票某一天的所有日均线
\*select avg(d.stock_close) into infiniteAverage from(
select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') order by t.stock_date desc
) d;
update stock_moving_average t set t.infinite=round(infiniteAverage,2) where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
*\end loop;
end;
end WRITE_MOVING_AVERAGE_BY_DATE;*/
/*------------------------------------------------ select bull rank stock ------------------------------------------------------*/
PROCEDURE SELECT_BULL_RANK_STOCK(
    p_date IN VARCHAR2,
    p_num OUT NUMBER)
AS
  -- define cursor section.返回全部在某一日均线成多头排列的股票
  CURSOR cur_bull_rank_stock_code
  IS
    SELECT DISTINCT *
    FROM stock_transaction_data t
    WHERE t.close_price >= t.ma5
    AND t.close_price   >= t.ma10
    AND t.close_price   >= t.ma20
    AND t.close_price   >= t.ma60
    AND t.close_price   >= t.ma120
    AND t.ma5           >= t.ma10
    AND t.ma5           >= t.ma20
    AND t.ma5           >= t.ma60
    AND t.ma5           >= t.ma120
    AND t.ma10          >= t.ma20
    AND t.ma10          >= t.ma60
    AND t.ma10          >= t.ma120
    AND t.ma20          >= t.ma60
    AND t.ma20          >= t.ma120
    AND t.ma60          >= t.ma120
    AND t.date_          = to_date(p_date, 'yyyy-mm-dd');
BEGIN
  p_num := 0;
  FOR i IN cur_bull_rank_stock_code
  LOOP
    dbms_output.put_line('code_   ' || i.code_);
    p_num := p_num + 1;
  END LOOP;
END SELECT_BULL_RANK_STOCK;
/*------------------------------------------------ select stock by three conditions ------------------------------------------------------*/
PROCEDURE SELECT_REVERSE_STOCK(
    p_date       IN VARCHAR2,
    p_percentage IN NUMBER,
    p_result_stock_code OUT T_TYPE_ARRAY)
AS
  -- 表示stock_code
  v_code VARCHAR2(10);
  -- 表示某只股票历史上的最高收盘价
  v_max_stock_close NUMBER;
  -- 表示当前股票某一日的收盘价
  v_current_stock_close NUMBER;
  -- 表示当前股票某一日的120均线价格
  v_current_stock_120 NUMBER;
  -- 用于计算某只股票的120均线是否在20个交易日内不是单调递减的
  v_num NUMBER;
  -- 定义一个字符型数据的数组，用于存储被选中的股票的代码。TYPE_ARRAY的定义在Type目录中。
  --type_array_stock_code type_array:=type_array(3000);
  -- define cursor section.返回全部在某一日均线成多头排列的股票
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT *
    FROM stock_transaction_data t
    WHERE t.date_ = to_date(p_date, 'yyyy-mm-dd');
  -- define cursor section.返回全部在某一支股票在某一日之后（包括某一日）的20个交易日内的交易记录
  CURSOR cur_top_twenty_stock_close
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
      ORDER BY t.date_ DESC
      ) b
  WHERE rownum <= 20;
  -- 创建UTL_FILE.file_type对象，用于读写文件
  file_handle UTL_FILE.file_type;
BEGIN
  --------------------------------- standard for loop section --------------------------------------------------------
  file_handle         := UTL_FILE.FOPEN('TXTDIR', 'SELECT_REVERSE_STOCK.txt', 'w');
  p_result_stock_code := t_type_array(51200);
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- 查询某只股票历史上的最高收盘价
    SELECT MAX(t.close_price)
    INTO v_max_stock_close
    FROM stock_transaction_data t
    WHERE t.code_ = v_code;
    -- 查询某只股票在某一日的收盘价格和120均线价格
    SELECT t.close_price,
      t.ma120
    INTO v_current_stock_close,
      v_current_stock_120
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
    v_num        := 0;
    -- 计算某只股票的某一日的收盘价格是否是其历史最高价的百分子：1-percentage
    IF (v_max_stock_close - v_current_stock_close) / v_max_stock_close > p_percentage THEN
      FOR j              IN cur_top_twenty_stock_close
      LOOP
        -- 判断某只股票的120均线是否在20个交易日内不是单调递减的
        IF v_current_stock_120 < j.ma120 THEN
          EXIT;
        END IF;
        v_current_stock_120 := j.ma120;
        p_result_stock_code.extend;
        v_num                                            := 1 + v_num;
        IF v_num                                          = 20 THEN
          p_result_stock_code(p_result_stock_code.count) := j.code_;
          --dbms_output.put_line('j.stock_code'||'  :  '||j.stock_code);
          UTL_FILE.PUT_LINE(file_handle, j.code_);
        END IF;
      END LOOP;
    END IF;
  END LOOP;
  UTL_FILE.FCLOSE(file_handle);
END SELECT_REVERSE_STOCK;
-- end procedure
/*------------------------------------------------ select stock by three conditions ------------------------------------------------------*/
/*procedure SELECT_STOCK_3(stockDate in varchar2,percentage in number) as
begin
------------------------------------------ standard declare section ---------------------------------------------------
declare
-- 表示stock_code
stockCode varchar2(10);
-- 表示某只股票历史上的最高收盘价
maxStockClose number;
-- 表示当前股票某一日的收盘价
currentStockClose number;
-- 表示当前股票某一日的120均线价格
currentStockOneHundredTwenty number;
-- 用于计算某只股票的120均线是否在20个交易日内不是单调递减的
num number;
-- 定义一个字符型数据的数组，用于存储被选中的股票的代码。TYPE_ARRAY的定义在Type目录中。
arrayStockCode t_type_array:=t_type_array();
-- define cursor section.返回全部在某一日均线成多头排列的股票
cursor allStockCode is select distinct * from stock_moving_average t
where t.stock_close>=t.five and t.stock_close>=t.ten and t.stock_close>=t.twenty and t.stock_close>=t.sixty and t.stock_close>=t.one_hundred_twenty
and t.five>=t.ten and t.five>=t.twenty and t.five>=t.sixty and t.five>=t.one_hundred_twenty
and t.ten>=t.twenty and t.ten>=t.sixty and t.ten>=t.one_hundred_twenty
and t.twenty>=t.sixty and t.twenty>=t.one_hundred_twenty
and t.sixty>=t.one_hundred_twenty
and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
-- define cursor section.返回全部在某一支股票在某一日之后（包括某一日）的20个交易日内的交易记录
cursor topTwentyStockClose is select * from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd')) b where rownum<=20 order by b.stock_date desc;
-- 创建UTL_FILE.file_type对象，用于读写文件
file_handle UTL_FILE.file_type;
------------------------------------------ standard declare section ---------------------------------------------------
begin
--------------------------------- standard for loop section --------------------------------------------------------
file_handle := UTL_FILE.FOPEN('TXTDIR','SELECT_STOCK_3.txt','w');
for i in allStockCode loop
stockCode:=i.stock_code;
-- 查询某只股票历史上的最高收盘价
select max(t.stock_close) into maxStockClose from stock_moving_average t where t.stock_code=stockCode;
-- 查询某只股票在某一日的收盘价格和120均线价格
select t.stock_close,t.one_hundred_twenty into currentStockClose,currentStockOneHundredTwenty from stock_moving_average t where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
num:=0;
-- 计算某只股票的某一日的收盘价格是否是其历史最高价的百分子：1-percentage
if (maxStockClose-currentStockClose)/maxStockClose>percentage then
for j in topTwentyStockClose loop
-- 判断某只股票的120均线是否在20个交易日内不是单调递减的
if currentStockOneHundredTwenty<j.one_hundred_twenty then
exit;
end if;
currentStockOneHundredTwenty:=j.one_hundred_twenty;
arrayStockCode.extend;
num:=1+num;
if num=20 then
arrayStockCode(arrayStockCode.count):=j.stock_code;
dbms_output.put_line('j.stock_code'||'  :  '||j.stock_code);
UTL_FILE.PUT_LINE(file_handle,j.stock_code);
end if;
end loop;
end if;
end loop;
UTL_FILE.FCLOSE(file_handle);
end;
end SELECT_STOCK_3;*/
/*------------------------------ judge whether stock is supported by 250 moving average ----------------------------------*/
PROCEDURE SELECT_STOCK_WITH_250_SUPPORT(
    p_date      IN VARCHAR2,
    p_near_rate IN NUMBER,
    p_down_rate IN NUMBER,
    p_stock_result_array OUT T_STOCK_RESULT_ARRAY)
AS
  -- 表示某只股票历史上的最高收盘价
  v_max_stock_close NUMBER;
  -- T_STOCK_RESULT类型对象。表示出现异动的股票
  type_stock_result T_STOCK_RESULT;
  -- 获取某一日的所有股票stock_code
  CURSOR cur_max_stock_close
  IS
    SELECT MAX(o.close_price) AS max_stock_close,
      o.code_,
      (SELECT b.ma250
      FROM stock_transaction_data b
      WHERE b.date_ = to_date(p_date, 'yyyy-mm-dd')
      AND b.code_   = o.code_
      ) AS two_hundred_fifty,
    (SELECT c.close_price
    FROM stock_transaction_data c
    WHERE c.date_ = to_date(p_date, 'yyyy-mm-dd')
    AND c.code_   = o.code_
    ) AS stock_close
  FROM stock_transaction_data o
  WHERE o.code_ IN
    (SELECT t.code_
    FROM stock_transaction_data t
    WHERE t.date_ = to_date(p_date, 'yyyy-mm-dd')
    )
  GROUP BY o.code_;
  -- 创建UTL_FILE.file_type对象，用于读写文件
  file_handle UTL_FILE.file_type;
BEGIN
  file_handle          := UTL_FILE.FOPEN('TXTDIR', 'SELECT_STOCK_WITH_250_SUPPORT.txt', 'w');
  p_stock_result_array := T_STOCK_RESULT_ARRAY();
  FOR i IN cur_max_stock_close
  LOOP
    v_max_stock_close                                                     := i.max_stock_close;
    IF (v_max_stock_close - i.stock_close) / v_max_stock_close             > p_down_rate THEN
      IF i.two_hundred_fifty                                              IS NOT NULL THEN
        IF ABS(i.stock_close - i.two_hundred_fifty) / i.two_hundred_fifty <= p_near_rate THEN
          dbms_output.put_line(i.code_);
          type_stock_result := T_STOCK_RESULT(i.code_, to_date(p_date, 'yyyy-mm-dd'));
          p_stock_result_array.extend;
          p_stock_result_array(p_stock_result_array.count) := type_stock_result;
          UTL_FILE.PUT_LINE(file_handle, p_stock_result_array(p_stock_result_array.count) .get_code() || '   ' || p_stock_result_array(p_stock_result_array.count) .get_date());
        END IF;
      END IF;
    END IF;
  END LOOP;
END SELECT_STOCK_WITH_250_SUPPORT;
/*------------------------------ judge whether stock is supported by 120 moving average ----------------------------------*/
PROCEDURE SELECT_STOCK_WITH_120_SUPPORT(
    p_date      IN VARCHAR2,
    p_near_rate IN NUMBER,
    p_down_rate IN NUMBER,
    p_stock_result_array OUT T_STOCK_RESULT_ARRAY)
AS
  -- 表示某只股票历史上的最高收盘价
  v_max_stock_close NUMBER;
  -- T_STOCK_RESULT类型对象。表示出现异动的股票
  type_stock_result T_STOCK_RESULT;
  -- 获取某一日的所有股票stock_code
  CURSOR cur_max_stock_close
  IS
    SELECT MAX(o.close_price) AS max_stock_close,
      o.code_,
      (SELECT b.ma120
      FROM stock_transaction_data b
      WHERE b.date_ = to_date(p_date, 'yyyy-mm-dd')
      AND b.code_   = o.code_
      ) AS one_hundred_twenty,
    (SELECT c.close_price
    FROM stock_transaction_data c
    WHERE c.date_ = to_date(p_date, 'yyyy-mm-dd')
    AND c.code_   = o.code_
    ) AS close_price
  FROM stock_transaction_data o
  WHERE o.code_ IN
    (SELECT t.code_
    FROM stock_transaction_data t
    WHERE t.date_ = to_date(p_date, 'yyyy-mm-dd')
    )
  GROUP BY o.code_;
  -- 创建UTL_FILE.file_type对象，用于读写文件
  file_handle UTL_FILE.file_type;
BEGIN
  file_handle          := UTL_FILE.FOPEN('TXTDIR', 'SELECT_STOCK_WITH_120_SUPPORT.txt', 'w');
  p_stock_result_array := T_STOCK_RESULT_ARRAY();
  FOR i IN cur_max_stock_close
  LOOP
    v_max_stock_close                                                       := i.max_stock_close;
    IF (v_max_stock_close - i.close_price) / v_max_stock_close               > p_down_rate THEN
      IF i.one_hundred_twenty                                               IS NOT NULL THEN
        IF ABS(i.close_price - i.one_hundred_twenty) / i.one_hundred_twenty <= p_near_rate THEN
          dbms_output.put_line(i.code_);
          type_stock_result := T_STOCK_RESULT(i.code_, to_date(p_date, 'yyyy-mm-dd'));
          p_stock_result_array.extend;
          p_stock_result_array(p_stock_result_array.count) := type_stock_result;
          UTL_FILE.PUT_LINE(file_handle, p_stock_result_array(p_stock_result_array.count) .get_code() || '   ' || p_stock_result_array(p_stock_result_array.count) .get_date());
        END IF;
      END IF;
    END IF;
  END LOOP;
END SELECT_STOCK_WITH_120_SUPPORT;
/*------------------------------ judge if MACD of the stock is the gold cross by date -----------------------------------------*/
PROCEDURE SELECT_MACD_GOLD_CROSS(
    p_date IN VARCHAR2,
    p_rate IN NUMBER,
    p_stock_macd_result OUT T_TYPE_ARRAY)
AS
  -- 定义一个stock_transaction_data%rowtype类型的变量
  row_stock_record stock_transaction_data%rowtype;
  -- 定义一个含有5个数值型数据的数组
type type_array IS varray(3) OF stock_transaction_data%rowtype;
type_array_dif_dea type_array := type_array();
-- 表示code_
v_stock_code VARCHAR2(10);
-- define cursor section.返回全部stock_code
CURSOR cur_all_stock_code
IS
  SELECT DISTINCT t.code_ FROM stock_transaction_data t ORDER BY t.code_ ASC;
-- 获取某只股票某一日及之前一日的记录
CURSOR cur_last_two_day_stock_info
IS
  SELECT *
  FROM
    (SELECT *
    FROM stock_transaction_data t
    WHERE t.code_ = v_stock_code
    AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
    ORDER BY t.date_ DESC
    )
WHERE rownum <= 2;
-- 创建UTL_FILE.file_type对象，用于读写文件
file_handle UTL_FILE.file_type;
BEGIN
  p_stock_macd_result := SCOTT.T_TYPE_ARRAY();
  file_handle         := UTL_FILE.FOPEN('TXTDIR', 'SELECT_MACD_UP_BELOW_ZERO.txt', 'w');
  FOR i IN cur_all_stock_code
  LOOP
    v_stock_code       := i.code_;
    type_array_dif_dea := type_array();
    FOR j IN cur_last_two_day_stock_info
    LOOP
      row_stock_record.dif   := j.dif;
      row_stock_record.dea   := j.dea;
      row_stock_record.code_ := j.code_;
      type_array_dif_dea.extend;
      type_array_dif_dea(type_array_dif_dea.count) := row_stock_record;
    END LOOP;
    -- 由于有些股票是在p_date之后才上市交易，因此没有记录，即type_array_dif_dea.count!=2，所以此处要先判断一下
    IF type_array_dif_dea.count                                                                                                 = 2 THEN
      IF type_array_dif_dea(1).dif                                                                                              > type_array_dif_dea(1).dea AND type_array_dif_dea(2) .dif < type_array_dif_dea(2).dea THEN
        IF (type_array_dif_dea(1) .dif + type_array_dif_dea(1).dea + type_array_dif_dea(2).dif + type_array_dif_dea(2).dea) / 4 < p_rate THEN
          dbms_output.put_line(type_array_dif_dea(1).code_);
          p_stock_macd_result.extend;
          p_stock_macd_result(p_stock_macd_result.count) := type_array_dif_dea(1) .code_;
          UTL_FILE.PUT_LINE(file_handle, type_array_dif_dea(1).code_);
        END IF;
      END IF;
    END IF;
  END LOOP;
  UTL_FILE.FCLOSE(file_handle);
END SELECT_MACD_GOLD_CROSS;
/*-------------------------------------- judge whether the close of stock is up or down --------------------------------------------*/
/*-- stock_transaction_data表的up_down字段已经在获取数据时就计算好了，所以现在这个方法用不着了。 ---*/
PROCEDURE WRITE_UP_DOWN
AS
BEGIN
  ------------------------------------------ standard declare section ---------------------------------------------------
  DECLARE
    -- 表示stock_code
    stockCode VARCHAR2(10);
    -- 表示前一天的stock_close
    preStockClose NUMBER;
    -- 表示某只股票第一天的stock_close
    initStockClose NUMBER;
    -- define cursor section.返回全部stock_code。注意表STOCK_CODE的字段stock_code是大写的，所以要用函数lower
    CURSOR allStockCode
    IS
      SELECT DISTINCT t.stock_code
      FROM stock_moving_average t
      ORDER BY t.stock_code DESC;
    -- 返回某只股票所有交易日的收盘价
    CURSOR singleStockClose
    IS
      SELECT *
      FROM stock_moving_average t
      WHERE t.stock_code = stockCode
      ORDER BY t.stock_date ASC;
    ------------------------------------------ standard declare section ---------------------------------------------------
  BEGIN
    FOR i IN allStockCode
    LOOP
      stockCode := lower(i.stock_code);
      SELECT t.stock_close
      INTO initStockClose
      FROM stock_moving_average t
      WHERE t.stock_code = stockCode
      AND rownum        <= 1
      ORDER BY t.stock_date ASC;
      preStockClose := initStockClose;
      FOR j IN singleStockClose
      LOOP
        IF preStockClose = j.stock_close THEN
          --dbms_output.put_line('stockCode'||'  :  '||stockCode);
          UPDATE stock_moving_average t
          SET t.up_down      = 0
          WHERE t.stock_code = j.stock_code
          AND t.stock_date   = j.stock_date;
        elsif preStockClose  < j.stock_close THEN
          UPDATE stock_moving_average t
          SET t.up_down      = 1
          WHERE t.stock_code = j.stock_code
          AND t.stock_date   = j.stock_date;
          --dbms_output.put_line('stockCode'||'  :  '||stockCode);
        ELSE
          UPDATE stock_moving_average t
          SET t.up_down      = -1
          WHERE t.stock_code = j.stock_code
          AND t.stock_date   = j.stock_date;
          --dbms_output.put_line('stockCode'||'  :  '||stockCode);
        END IF;
        preStockClose := j.stock_close;
      END LOOP;
    END LOOP;
  END;
END WRITE_UP_DOWN;
/*------------------------------ judge whether the close of stock is up or down according the given date ------------------------------------*/
PROCEDURE WRITE_UP_DOWN_BY_DATE(
    stockDate IN VARCHAR2)
AS
  -- 表示stock_code
  stockCode VARCHAR2(10);
  -- 表示当前股票某一日的收盘价
  currentStockClose NUMBER;
  -- 表示当前股票某一日的日期
  currentStockDate DATE;
  -- define cursor section.返回全部stock_code。
  CURSOR allStockCode
  IS
    SELECT DISTINCT t.stock_code
    FROM stock_moving_average t
    ORDER BY t.stock_code ASC;
  -- 返回某只股票从某一日开始最近两天的交易记录
  CURSOR twoStock
  IS
    SELECT *
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    AND t.stock_date  <= to_date(stockDate, 'yyyy-mm-dd')
    AND rownum        <= 2
    ORDER BY t.stock_date DESC;
BEGIN
  FOR i IN allStockCode
  LOOP
    stockCode := i.stock_code;
    -- 返回某只股票某一日的收盘价和日期
    SELECT t.stock_close,
      t.stock_date
    INTO currentStockClose,
      currentStockDate
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    AND t.stock_date  <= to_date(stockDate, 'yyyy-mm-dd')
    AND rownum        <= 1
    ORDER BY t.stock_date DESC;
    FOR j IN twoStock
    LOOP
      IF currentStockDate != j.stock_date THEN
        IF j.stock_close   > currentStockClose THEN
          UPDATE stock_moving_average t
          SET t.up_down      = -1
          WHERE t.stock_code = i.stock_code
          AND t.stock_date   = to_date(stockDate, 'yyyy-mm-dd');
        elsif j.stock_close  < currentStockClose THEN
          UPDATE stock_moving_average t
          SET t.up_down      = 1
          WHERE t.stock_code = i.stock_code
          AND t.stock_date   = to_date(stockDate, 'yyyy-mm-dd');
        ELSE
          UPDATE stock_moving_average t
          SET t.up_down      = 0
          WHERE t.stock_code = i.stock_code
          AND t.stock_date   = to_date(stockDate, 'yyyy-mm-dd');
        END IF;
      END IF;
    END LOOP;
  END LOOP;
END WRITE_UP_DOWN_BY_DATE;
/*------------------------------------------------------ write MACD of all stocks ------------------------------------------------------*/
PROCEDURE WRITE_MACD_INIT
AS
  -- 表示code_
  v_code VARCHAR2(10);
  -- define cursor section.返回全部code_
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_ FROM stock_transaction_data t ORDER BY t.code_ ASC;
  -- 获取每只股票第一个交易日的日期
  CURSOR cur_first_stock_date
  IS
    SELECT MIN(t.date_) AS date_
    FROM stock_transaction_data t
    WHERE t.code_ = v_code;
BEGIN
  -- 初始化每只股票第一个交易日的ema12,ema26,dif和dea字段
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    FOR j IN cur_first_stock_date
    LOOP
      UPDATE stock_transaction_data t
      SET t.ema12   = t.close_price,
        t.ema26     = t.close_price,
        t.dif       = 0,
        t.dea       = 0
      WHERE t.code_ = v_code
      AND t.date_   = j.date_;
      COMMIT;
    END LOOP;
  END LOOP;
END WRITE_MACD_INIT;
PROCEDURE WRITE_MACD_EMA
AS
  v_pre_ema12  NUMBER;
  v_pre_ema26  NUMBER;
  v_pre_dif    NUMBER;
  v_pre_dea    NUMBER;
  v_first_date DATE;
  -- 表示stock_code
  v_code VARCHAR2(10);
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_ FROM stock_transaction_data t ORDER BY t.code_ ASC;
  -- 获取每只股票第一个交易日的日期
  CURSOR cur_first_stock_date
  IS
    SELECT MIN(t.date_) AS date_
    FROM stock_transaction_data t
    WHERE t.code_ = v_code;
  -- 根据v_code选出某只股票的除第一天以外的全部交易记录，按升序排列
  CURSOR cur_all_stock
  IS
    SELECT DISTINCT *
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    AND t.date_   > v_first_date
    ORDER BY t.date_ ASC;
BEGIN
  -- 计算每只股票其余交易日的ema12,ema26,dif和dea字段
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- 用记录是第一个交易日的字段初始化相关变量
    FOR x IN cur_first_stock_date
    LOOP
      SELECT t.ema12,
        t.ema26,
        t.dif,
        t.dea
      INTO v_pre_ema12,
        v_pre_ema26,
        v_pre_dif,
        v_pre_dea
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_   = x.date_;
    END LOOP;
    SELECT MIN(t.date_)
    INTO v_first_date
    FROM stock_transaction_data t
    WHERE t.code_ = v_code;
    FOR j IN cur_all_stock
    LOOP
      -- 对于其余交易日，更新ema12,ema26,dif和dea字段
      UPDATE stock_transaction_data t
      SET t.ema12   = v_pre_ema12 * 11 / 13 + j.close_price * 2 / 13,
        t.ema26     = v_pre_ema26 * 25 / 27 + j.close_price * 2 / 27
      WHERE t.code_ = v_code
      AND t.date_   = j.date_;
      COMMIT;
      -- 用于计算下一个交易日时使用
      SELECT t.ema12
      INTO v_pre_ema12
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_   = j.date_;
      SELECT t.ema26
      INTO v_pre_ema26
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_   = j.date_;
    END LOOP;
  END LOOP;
END WRITE_MACD_EMA;
PROCEDURE WRITE_MACD_DIF
AS
  v_pre_ema12  NUMBER;
  v_pre_ema26  NUMBER;
  v_pre_dif    NUMBER;
  v_pre_dea    NUMBER;
  v_first_date DATE;
  -- 表示stock_code
  v_code VARCHAR2(10);
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_ FROM stock_transaction_data t ORDER BY t.code_ ASC;
  -- 获取每只股票第一个交易日的日期
  CURSOR cur_first_stock_date
  IS
    SELECT MIN(t.date_) AS date_
    FROM stock_transaction_data t
    WHERE t.code_ = v_code;
  -- 根据v_code选出某只股票的除第一天以外的全部交易记录，按升序排列
  CURSOR cur_all_stock
  IS
    SELECT DISTINCT *
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    AND t.date_   > v_first_date
    ORDER BY t.date_ ASC;
BEGIN
  -- 计算每只股票其余交易日的ema12,ema26,dif和dea字段
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- 用记录是第一个交易日的字段初始化相关变量
    FOR x IN cur_first_stock_date
    LOOP
      SELECT t.ema12,
        t.ema26,
        t.dif,
        t.dea
      INTO v_pre_ema12,
        v_pre_ema26,
        v_pre_dif,
        v_pre_dea
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_   = x.date_;
    END LOOP;
    SELECT MIN(t.date_)
    INTO v_first_date
    FROM stock_transaction_data t
    WHERE t.code_ = v_code;
    FOR j IN cur_all_stock
    LOOP
      UPDATE stock_transaction_data t
      SET t.dif     = t.ema12 - t.ema26
      WHERE t.code_ = v_code
      AND t.date_   = j.date_;
      COMMIT;
      SELECT t.dif
      INTO v_pre_dif
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_   = j.date_;
    END LOOP;
  END LOOP;
END WRITE_MACD_DIF;
PROCEDURE WRITE_MACD_DEA
AS
  v_pre_ema12  NUMBER;
  v_pre_ema26  NUMBER;
  v_pre_dif    NUMBER;
  v_pre_dea    NUMBER;
  v_first_date DATE;
  -- 表示code_
  v_code VARCHAR2(10);
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_ FROM stock_transaction_data t ORDER BY t.code_ ASC;
  -- 获取每只股票第一个交易日的日期
  CURSOR cur_first_stock_date
  IS
    SELECT MIN(t.date_) AS date_
    FROM stock_transaction_data t
    WHERE t.code_ = v_code;
  -- 根据v_code选出某只股票的除第一天以外的全部交易记录，按升序排列
  CURSOR cur_all_stock
  IS
    SELECT DISTINCT *
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    AND t.date_   > v_first_date
    ORDER BY t.date_ ASC;
BEGIN
  -- 计算每只股票其余交易日的ema12,ema26,dif和dea字段
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- 用记录是第一个交易日的字段初始化相关变量
    FOR x IN cur_first_stock_date
    LOOP
      SELECT t.ema12,
        t.ema26,
        t.dif,
        t.dea
      INTO v_pre_ema12,
        v_pre_ema26,
        v_pre_dif,
        v_pre_dea
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_   = x.date_;
    END LOOP;
    SELECT MIN(t.date_)
    INTO v_first_date
    FROM stock_transaction_data t
    WHERE t.code_ = v_code;
    FOR j IN cur_all_stock
    LOOP
      UPDATE stock_transaction_data t
      SET t.dea     = v_pre_dea * 8 / 10 + t.dif * 2 / 10
      WHERE t.code_ = v_code
      AND t.date_   = j.date_;
      COMMIT;
      SELECT t.dea
      INTO v_pre_dea
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_   = j.date_;
    END LOOP;
  END LOOP;
END WRITE_MACD_DEA;
/*-------------------------------- write MACD of all stocks by date -----------------------------------------------*/
PROCEDURE WRITE_MACD_EMA_BY_DATE(
    p_date IN VARCHAR2)
AS
  v_pre_ema12 NUMBER;
  v_pre_ema26 NUMBER;
  v_pre_dea   NUMBER;
  -- 表示code_
  v_code VARCHAR2(10);
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_
    FROM stock_transaction_data_all t
    ORDER BY t.code_ ASC;
  -- 获取某只股票最近两天的记录
  CURSOR cur_single_stock
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
      ORDER BY t.date_ DESC
      )
  WHERE rownum <= 2;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    FOR j IN cur_single_stock
    LOOP
      IF j.date_    != to_date(p_date, 'yyyy-mm-dd') THEN
        v_pre_ema12 := j.ema12;
        v_pre_ema26 := j.ema26;
        v_pre_dea   := j.dea;
        UPDATE stock_transaction_data t
        SET t.ema12   = v_pre_ema12 * 11 / 13 + t.close_price * 2 / 13,
          t.ema26     = v_pre_ema26 * 25 / 27 + t.close_price * 2 / 27
        WHERE t.code_ = v_code
        AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
        COMMIT;
      END IF;
    END LOOP;
  END LOOP;
END WRITE_MACD_EMA_BY_DATE;
PROCEDURE WRITE_MACD_DIF_BY_DATE(
    p_date IN VARCHAR2)
AS
  v_pre_ema12 NUMBER;
  v_pre_ema26 NUMBER;
  v_pre_dea   NUMBER;
  -- 表示stock_code
  v_code VARCHAR2(10);
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_
    FROM stock_transaction_data_all t
    ORDER BY t.code_ ASC;
  -- 获取某只股票最近两天的记录
  CURSOR cur_single_stock
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
      ORDER BY t.date_ DESC
      )
  WHERE rownum <= 2;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    FOR j IN cur_single_stock
    LOOP
      IF j.date_    != to_date(p_date, 'yyyy-mm-dd') THEN
        v_pre_ema12 := j.ema12;
        v_pre_ema26 := j.ema26;
        v_pre_dea   := j.dea;
        UPDATE stock_transaction_data t
        SET t.dif     = t.ema12 - t.ema26
        WHERE t.code_ = v_code
        AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
        COMMIT;
      END IF;
    END LOOP;
  END LOOP;
END WRITE_MACD_DIF_BY_DATE;
PROCEDURE WRITE_MACD_DEA_BY_DATE(
    p_date IN VARCHAR2)
AS
  v_pre_ema12 NUMBER;
  v_pre_ema26 NUMBER;
  v_pre_dea   NUMBER;
  -- 表示stock_code
  v_code VARCHAR2(10);
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_
    FROM stock_transaction_data_all t
    ORDER BY t.code_ ASC;
  -- 获取某只股票最近两天的记录
  CURSOR cur_single_stock
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
      ORDER BY t.date_ DESC
      )
  WHERE rownum <= 2;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    FOR j IN cur_single_stock
    LOOP
      IF j.date_    != to_date(p_date, 'yyyy-mm-dd') THEN
        v_pre_ema12 := j.ema12;
        v_pre_ema26 := j.ema26;
        v_pre_dea   := j.dea;
        UPDATE stock_transaction_data t
        SET t.dea     = v_pre_dea * 8 / 10 + t.dif * 2 / 10
        WHERE t.code_ = v_code
        AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
        COMMIT;
      END IF;
    END LOOP;
  END LOOP;
END WRITE_MACD_DEA_BY_DATE;
/*--------------------------------- 计算日线级别所有股票的KD指标 ----------------------------*/
PROCEDURE WRITE_KD_INIT
AS
  -- 表示某只股票的code_字段
  v_code VARCHAR2(10);
  -- 用于计算是否是第9个交易周
  num NUMBER;
  -- define cursor section.返回全部code_
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_ FROM stock_transaction_data t ORDER BY t.code_ ASC;
  -- 查询某只股票最初的8个交易周的记录，并按生序排列
  CURSOR cur_single_stock_k
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      ORDER BY t.date_ ASC
      )
  WHERE rownum <= 8;
BEGIN
  -- 初始化每只股票第一个交易日的K和D字段
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    num    := 0;
    FOR j IN cur_single_stock_k
    LOOP
      num   := num + 1;
      IF num = 8 THEN
        -- 若无前一日K值与D值，则可分别用50来代替
        UPDATE stock_transaction_data t
        SET t.k       = 50,
          t.d         = 50
        WHERE t.code_ = v_code
        AND t.date_   = j.date_;
        COMMIT;
      END IF;
    END LOOP;
  END LOOP;
END WRITE_KD_INIT;
PROCEDURE WRITE_KD_RSV
AS
  -- 表示某只股票的code_字段
  v_code VARCHAR2(10);
  -- 9日内最高价
  v_nine_day_highest_price NUMBER;
  -- 9日内最低价
  v_nine_day_lowest_price NUMBER;
  -- 计算指标时使用，用于表示日期的累积
  num NUMBER;
  -- define cursor section.返回全部code_
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_ FROM stock_transaction_data t ORDER BY t.code_ ASC;
  -- 获取某只股票所有的日线级别的交易记录，并按升序排列
  CURSOR cur_single_stock
  IS
    SELECT *
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    ORDER BY t.date_ ASC;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    num    := 0;
    FOR j IN cur_single_stock
    LOOP
      num    := num + 1;
      IF num >= 9 THEN
        -- 计算9日内最高价和最低价
        SELECT MAX(highest_price),
          MIN(lowest_price)
        INTO v_nine_day_highest_price,
          v_nine_day_lowest_price
        FROM
          (SELECT *
          FROM stock_transaction_data t
          WHERE t.code_ = v_code
          AND t.date_  <= j.date_
          ORDER BY t.date_ DESC
          )
        WHERE rownum <= 9;
        -- 计算rsv
        IF (v_nine_day_highest_price - v_nine_day_lowest_price) = 0 THEN
          UPDATE stock_transaction_data t
          SET t.rsv =
            (SELECT t1.rsv
            FROM
              (SELECT *
              FROM stock_transaction_data t
              WHERE t.code_ = v_code
              AND t.date_   < j.date_
              ORDER BY t.date_ DESC
              ) t1
            WHERE rownum<=1
            )
          WHERE t.code_ = v_code
          AND t.date_   = j.date_;
        ELSE
          UPDATE stock_transaction_data t
          SET t.rsv     = (t.close_price - v_nine_day_lowest_price) / (v_nine_day_highest_price - v_nine_day_lowest_price) * 100
          WHERE t.code_ = v_code
          AND t.date_   = j.date_;
        END IF;
        COMMIT;
      END IF;
    END LOOP;
  END LOOP;
END WRITE_KD_RSV;
PROCEDURE WRITE_KD_K
AS
  -- 表示某只股票的CODE_字段
  v_code VARCHAR2(10);
  -- 计算指标时使用，用于表示日期的累积
  num NUMBER;
  -- 表示KD指标的K
  v_temp_k NUMBER;
  -- define cursor section.返回全部code_
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_ FROM stock_transaction_data t ORDER BY t.code_ ASC;
  -- 获取某只股票所有的日线级别的交易记录，并按升序排列
  CURSOR cur_single_stock
  IS
    SELECT *
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    ORDER BY t.date_ ASC;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    num    := 0;
    FOR j IN cur_single_stock
    LOOP
      num        := num + 1;
      IF num      = 8 THEN
        v_temp_k := j.k;
      END IF;
      IF num >= 9 THEN
        -- 计算K
        UPDATE stock_transaction_data t
        SET t.k       = 2 / 3 * v_temp_k + 1 / 3 * t.rsv
        WHERE t.code_ = v_code
        AND t.date_   = j.date_;
        COMMIT;
        SELECT t.k
        INTO v_temp_k
        FROM stock_transaction_data t
        WHERE t.code_ = v_code
        AND t.date_   = j.date_;
      END IF;
    END LOOP;
  END LOOP;
END WRITE_KD_K;
PROCEDURE WRITE_KD_D
AS
  -- 表示某只股票的code_字段
  v_code VARCHAR2(10);
  -- 计算指标时使用，用于表示日期的累积
  num NUMBER;
  -- 表示KD指标的D
  v_temp_d NUMBER;
  -- define cursor section.返回全部code_
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_ FROM stock_transaction_data t ORDER BY t.code_ ASC;
  -- 获取某只股票所有的日线级别的交易记录，并按升序排列
  CURSOR cur_single_stock
  IS
    SELECT *
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    ORDER BY t.date_ ASC;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    num    := 0;
    FOR j IN cur_single_stock
    LOOP
      num        := num + 1;
      IF num      = 8 THEN
        v_temp_d := j.d;
      END IF;
      IF num >= 9 THEN
        -- 计算D
        UPDATE stock_transaction_data t
        SET t.d       = 2 / 3 * v_temp_d + 1 / 3 * j.k
        WHERE t.code_ = v_code
        AND t.date_   = j.date_;
        COMMIT;
        SELECT t.d
        INTO v_temp_d
        FROM stock_transaction_data t
        WHERE t.code_ = v_code
        AND t.date_   = j.date_;
      END IF;
    END LOOP;
  END LOOP;
END WRITE_KD_D;
---------------------------------- 计算日线级别，某一日，所有股票的KD指标 ----------------------------
PROCEDURE WRITE_KD_BY_DATE_RSV(
    p_date VARCHAR2)
AS
  -- 表示某只股票的code_字段
  v_code VARCHAR2(10);
  -- 9日内最高价
  v_nine_day_highest_price NUMBER;
  -- 9日内最低价
  v_nine_day_lowest_price NUMBER;
  -- define cursor section.返回全部code_
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_
    FROM stock_transaction_data_all t
    ORDER BY t.code_ ASC;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- 计算9日内最高价和最低价
    SELECT MAX(highest_price),
      MIN(lowest_price)
    INTO v_nine_day_highest_price,
      v_nine_day_lowest_price
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
      ORDER BY t.date_ DESC
      )
    WHERE rownum <= 9;
    -- 计算某只股票某一日的RSV
    UPDATE stock_transaction_data t
    SET t.rsv     = (t.close_price - v_nine_day_lowest_price) / (v_nine_day_highest_price - v_nine_day_lowest_price) * 100
    WHERE t.code_ = v_code
    AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
    COMMIT;
  END LOOP;
END WRITE_KD_BY_DATE_RSV;
PROCEDURE WRITE_KD_BY_DATE_K(
    p_date VARCHAR2)
AS
  -- 表示某只股票的code_字段
  v_code VARCHAR2(10);
  -- 表示前一日K值
  v_temp_k NUMBER;
  -- 用于计数
  v_num NUMBER;
  -- define cursor section.返回全部code_
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_
    FROM stock_transaction_data_all t
    ORDER BY t.code_ ASC;
  -- 按照日期参数，获取某只股票最近两天的日线级别的交易记录，并按降序排列
  CURSOR cur_single_stock
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
      ORDER BY t.date_ DESC
      )
  WHERE rownum <= 2
  ORDER BY date_ ASC;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    v_num  := 0;
    FOR j IN cur_single_stock
    LOOP
      v_num      := v_num + 1;
      IF v_num    = 1 THEN
        v_temp_k := j.k;
      END IF;
      IF v_num = 2 THEN
        UPDATE stock_transaction_data t
        SET t.k       = 2 / 3 * v_temp_k + 1 / 3 * t.rsv
        WHERE t.code_ = v_code
        AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
        COMMIT;
      END IF;
    END LOOP;
  END LOOP;
END WRITE_KD_BY_DATE_K;
PROCEDURE WRITE_KD_BY_DATE_D(
    p_date VARCHAR2)
AS
  -- 表示某只股票的code_字段
  v_code VARCHAR2(10);
  -- 表示前一日K值
  v_temp_d NUMBER;
  -- 用于计数
  v_num NUMBER;
  -- define cursor section.返回全部code_
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_
    FROM stock_transaction_data_all t
    ORDER BY t.code_ ASC;
  -- 按照日期参数，获取某只股票最近两天的日线级别的交易记录，并按降序排列
  CURSOR cur_single_stock
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
      ORDER BY t.date_ DESC
      )
  WHERE rownum <= 2
  ORDER BY date_ ASC;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    v_num  := 0;
    FOR j IN cur_single_stock
    LOOP
      v_num      := v_num + 1;
      IF v_num    = 1 THEN
        v_temp_d := j.d;
      END IF;
      IF v_num = 2 THEN
        UPDATE stock_transaction_data t
        SET t.d       = 2 / 3 * v_temp_d + 1 / 3 * t.k
        WHERE t.code_ = v_code
        AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
        COMMIT;
      END IF;
    END LOOP;
  END LOOP;
END WRITE_KD_BY_DATE_D;
/*------------------------------ 计算所有股票的ha -----------------------------------------*/
PROCEDURE WRITE_HA
AS
  -- 代码
  v_code VARCHAR2(10);
  -- 日期
  v_date DATE;
  -- 获取所有的CODE
  CURSOR cur_all_code
  IS
    SELECT DISTINCT t.code_ code FROM stock_transaction_data t;
  -- 查询某个具体的指数的第一条交易记录
  CURSOR cur_fist_stda
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      ORDER BY t.date_ ASC
      )
  WHERE rownum <= 1;
  -- 查询某个股票除了最早的一条记录外的其他记录，并按升序排列
  CURSOR cur_later_stda
  IS
    SELECT *
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    AND t.date_   > v_date
    ORDER BY t.date_ ASC;
  -- 定义表stock_transaction_data_all结构的游标变量
  first_stda stock_transaction_data_all%rowtype;
  later_stda stock_transaction_data_all%rowtype;
  pre_stda stock_transaction_data_all%rowtype;
  -- 用于计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价的变量
  v_ha_open_price    NUMBER;
  v_ha_close_price   NUMBER;
  v_ha_highest_price NUMBER;
  v_ha_lowest_price  NUMBER;
BEGIN
  OPEN cur_all_code;
  LOOP
    -- 获取每个股票的code_字段
    FETCH cur_all_code INTO v_code;
    EXIT
  WHEN cur_all_code%notfound;
    -- 先计算每个股票的第一条记录
    OPEN cur_fist_stda;
    FETCH cur_fist_stda INTO first_stda;
    EXIT
  WHEN cur_fist_stda%notfound;
    -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
    v_ha_open_price            := (first_stda.open_price + first_stda.close_price) / 2;
    v_ha_close_price           := (first_stda.open_price + first_stda.close_price + first_stda.highest_price + first_stda.lowest_price) / 4;
    IF first_stda.highest_price > v_ha_open_price THEN
      v_ha_highest_price       := first_stda.highest_price;
    ELSE
      v_ha_highest_price := v_ha_open_price;
    END IF;
    IF first_stda.lowest_price < v_ha_open_price THEN
      v_ha_lowest_price       := first_stda.lowest_price;
    ELSE
      v_ha_lowest_price := v_ha_open_price;
    END IF;
    -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
    UPDATE stock_transaction_data t
    SET t.ha_open_price  = v_ha_open_price,
      t.ha_close_price   = v_ha_close_price,
      t.ha_highest_price = v_ha_highest_price,
      t.ha_lowest_price  = v_ha_lowest_price
    WHERE t.code_        = first_stda.code_
    AND t.date_          = first_stda.date_;
    COMMIT;
    v_date := first_stda.date_;
    CLOSE cur_fist_stda;
    -- 再计算每个股票的其他记录
    OPEN cur_later_stda;
    LOOP
      FETCH cur_later_stda INTO later_stda;
      EXIT
    WHEN cur_later_stda%notfound;
      -- 前一条记录
      SELECT *
      INTO pre_stda
      FROM
        (SELECT *
        FROM stock_transaction_data t
        WHERE t.code_ = v_code
        AND t.date_  <= v_date
        ORDER BY t.date_ DESC
        )
      WHERE rownum <= 1;
      -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
      v_ha_open_price            := (pre_stda.ha_open_price + pre_stda.ha_close_price) / 2;
      v_ha_close_price           := (later_stda.open_price  + later_stda.close_price + later_stda.highest_price + later_stda.lowest_price) / 4;
      IF later_stda.highest_price > v_ha_open_price THEN
        v_ha_highest_price       := later_stda.highest_price;
      ELSE
        v_ha_highest_price := v_ha_open_price;
      END IF;
      IF later_stda.lowest_price < v_ha_open_price THEN
        v_ha_lowest_price       := later_stda.lowest_price;
      ELSE
        v_ha_lowest_price := v_ha_open_price;
      END IF;
      -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
      UPDATE stock_transaction_data t
      SET t.ha_open_price  = v_ha_open_price,
        t.ha_close_price   = v_ha_close_price,
        t.ha_highest_price = v_ha_highest_price,
        t.ha_lowest_price  = v_ha_lowest_price
      WHERE t.code_        = later_stda.code_
      AND t.date_          = later_stda.date_;
      COMMIT;
      -- 把这次的数据留给下一次迭代使用
      v_date := later_stda.date_;
    END LOOP;
    CLOSE cur_later_stda;
  END LOOP;
  CLOSE cur_all_code;
END WRITE_HA;
/*------------------------------ 计算某一日所有股票的ha -----------------------------------------*/
PROCEDURE WRITE_HA_BY_DATE(
    p_date VARCHAR2)
AS
  -- 获取所有的CODE
  CURSOR cur_all_code
  IS
    SELECT DISTINCT t.code_ code FROM stock_transaction_data_all t;
  -- 表示CODE类型的变量
  v_code VARCHAR2(10);
  -- 查询某个指数在日期p_date之前的那一条记录
  CURSOR cur_all_std
  IS
    SELECT *
    FROM
      (SELECT *
      FROM
        (SELECT *
        FROM stock_transaction_data t
        WHERE t.code_ = v_code
        AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
        ORDER BY t.date_ DESC
        )
    WHERE rownum <= 2
      ) t2
    WHERE t2.date_ <>
      (SELECT t1.date_
      FROM stock_transaction_data t1
      WHERE t1.code_ = v_code
      AND t1.date_   = to_date(p_date, 'yyyy-mm-dd')
      );
    -- 定义表stock_transaction_data结构的游标变量
    all_std stock_transaction_data%rowtype;
    -- 查询某个具体的股票的某一日交易记录
    CURSOR cur_later_std
    IS
      SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
    -- 定义表stock_transaction_data结构的游标变量
    later_std stock_transaction_data%rowtype;
    -- 用于计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价的变量
    v_ha_open_price    NUMBER;
    v_ha_close_price   NUMBER;
    v_ha_highest_price NUMBER;
    v_ha_lowest_price  NUMBER;
  BEGIN
    OPEN cur_all_code;
    LOOP
      -- 获取每个股票的code_字段
      FETCH cur_all_code INTO v_code;
      EXIT
    WHEN cur_all_code%notfound;
      OPEN cur_all_std;
      OPEN cur_later_std;
      LOOP
        FETCH cur_all_std INTO all_std;
        EXIT
      WHEN cur_all_std%notfound;
        FETCH cur_later_std INTO later_std;
        EXIT
      WHEN cur_later_std%notfound;
        -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        v_ha_open_price           := (all_std.ha_open_price + all_std.ha_close_price) / 2;
        v_ha_close_price          := (later_std.open_price  + later_std.close_price + later_std.highest_price + later_std.lowest_price) / 4;
        IF later_std.highest_price > v_ha_open_price THEN
          v_ha_highest_price      := later_std.highest_price;
        ELSE
          v_ha_highest_price := v_ha_open_price;
        END IF;
        IF later_std.lowest_price < v_ha_open_price THEN
          v_ha_lowest_price      := later_std.lowest_price;
        ELSE
          v_ha_lowest_price := v_ha_open_price;
        END IF;
        -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        UPDATE stock_transaction_data t
        SET t.ha_open_price  = v_ha_open_price,
          t.ha_close_price   = v_ha_close_price,
          t.ha_highest_price = v_ha_highest_price,
          t.ha_lowest_price  = v_ha_lowest_price
        WHERE t.code_        = later_std.code_
        AND t.date_          = later_std.date_;
        COMMIT;
      END LOOP;
      CLOSE cur_all_std;
      CLOSE cur_later_std;
    END LOOP;
    CLOSE cur_all_code;
  END WRITE_HA_BY_DATE;
  /*---------------------------------------------------- 计算所有股票的乖离率 -------------------------------------------------*/
PROCEDURE WRITE_BIAS
IS
BEGIN
  DECLARE
    -- 表示code_
    v_code VARCHAR2(10);
    -- 5日乖离率
    v_bias5 NUMBER := NULL;
    -- 10日乖离率
    v_bias10 NUMBER := NULL;
    -- 20日乖离率
    v_bias20 NUMBER := NULL;
    -- 60日乖离率
    v_bias60 NUMBER := NULL;
    -- 120日乖离率
    v_bias120 NUMBER := NULL;
    -- 250日乖离率
    v_bias250 NUMBER := NULL;
    -- 交易日期
    v_date DATE;
    -- 获取股票的code_，排除重复
    CURSOR cur_all_stock_code
    IS
      SELECT DISTINCT t.code_ FROM stock_transaction_data t;
    -- 根据v_code，获取这个股票的所有数据，并按照升序排列
    CURSOR cur_single_stock
    IS
      SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      ORDER BY t.date_ ASC;
  BEGIN
    FOR i IN cur_all_stock_code
    LOOP
      v_code := i.code_;
      FOR j IN cur_single_stock
      LOOP
        v_date := j.date_;
        -- 5日乖离率
        IF j.ma5  IS NOT NULL THEN
          v_bias5 := (j.close_price - j.ma5) / j.ma5 * 100;
        ELSE
          v_bias5 := NULL;
        END IF;
        -- 10日乖离率
        IF j.ma10  IS NOT NULL THEN
          v_bias10 := (j.close_price - j.ma10) / j.ma10 * 100;
        ELSE
          v_bias10 := NULL;
        END IF;
        -- 20日乖离率
        IF j.ma20  IS NOT NULL THEN
          v_bias20 := (j.close_price - j.ma20) / j.ma20 * 100;
        ELSE
          v_bias20 := NULL;
        END IF;
        -- 60日乖离率
        IF j.ma60  IS NOT NULL THEN
          v_bias60 := (j.close_price - j.ma60) / j.ma60 * 100;
        ELSE
          v_bias60 := NULL;
        END IF;
        -- 120日乖离率
        IF j.ma120  IS NOT NULL THEN
          v_bias120 := (j.close_price - j.ma120) / j.ma120 * 100;
        ELSE
          v_bias120 := NULL;
        END IF;
        -- 250日乖离率
        IF j.ma250  IS NOT NULL THEN
          v_bias250 := (j.close_price - j.ma250) / j.ma250 * 100;
        ELSE
          v_bias250 := NULL;
        END IF;
        -- 更新
        UPDATE stock_transaction_data t
        SET t.bias5   = v_bias5,
          t.bias10    = v_bias10,
          t.bias20    = v_bias20,
          t.bias60    = v_bias60,
          t.bias120   = v_bias120,
          t.bias250   = v_bias250
        WHERE t.code_ = v_code
        AND t.date_   = v_date;
      END LOOP;
      COMMIT;
    END LOOP;
  END;
END WRITE_BIAS;
/*------------------------ 按照日期，计算所有股票在某一日的乖离率 -----------------------*/
PROCEDURE WRITE_BIAS_BY_DATE(
    p_date IN VARCHAR2)
IS
  -- 表示CODE_类型的变量
  v_code VARCHAR2(10);
  -- 5日乖离率
  v_bias5 NUMBER := NULL;
  -- 10日乖离率
  v_bias10 NUMBER := NULL;
  -- 20日乖离率
  v_bias20 NUMBER := NULL;
  -- 60日乖离率
  v_bias60 NUMBER := NULL;
  -- 120日乖离率
  v_bias120 NUMBER := NULL;
  -- 250日乖离率
  v_bias250 NUMBER := NULL;
  row_stock_transaction_data stock_transaction_data%rowtype;
  -- 获取所有股票的code_，排除重复的
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_ FROM stock_transaction_data t;
  -- 某个股票某一天的交易记录
  CURSOR cur_single_stock
  IS
    SELECT *
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
BEGIN
  OPEN cur_all_stock_code;
  LOOP
    -- 获取每个股票的code_字段
    FETCH cur_all_stock_code INTO v_code;
    EXIT
  WHEN cur_all_stock_code%notfound;
    OPEN cur_single_stock;
    LOOP
      -- 获取每个股票在某一日的交易记录
      FETCH cur_single_stock INTO row_stock_transaction_data;
      EXIT
    WHEN cur_single_stock%notfound;
      -- 计算5日乖离率、10日乖离率、20日乖离率、60日乖离率、120日乖离率、250日乖离率
      IF row_stock_transaction_data.ma5 IS NOT NULL THEN
        v_bias5                         := (row_stock_transaction_data.close_price -
        row_stock_transaction_data.ma5)                                            / row_stock_transaction_data.ma5 * 100;
      ELSE
        v_bias5 := NULL;
      END IF;
      IF row_stock_transaction_data.ma10 IS NOT NULL THEN
        v_bias10                         := (row_stock_transaction_data.close_price -
        row_stock_transaction_data.ma10)                                            / row_stock_transaction_data.ma10 * 100;
      ELSE
        v_bias10 := NULL;
      END IF;
      IF row_stock_transaction_data.ma20 IS NOT NULL THEN
        v_bias20                         := (row_stock_transaction_data.close_price -
        row_stock_transaction_data.ma20)                                            / row_stock_transaction_data.ma20 * 100;
      ELSE
        v_bias20 := NULL;
      END IF;
      IF row_stock_transaction_data.ma60 IS NOT NULL THEN
        v_bias60                         := (row_stock_transaction_data.close_price -
        row_stock_transaction_data.ma60)                                            / row_stock_transaction_data.ma60 * 100;
      ELSE
        v_bias60 := NULL;
      END IF;
      IF row_stock_transaction_data.ma120 IS NOT NULL THEN
        v_bias120                         := (row_stock_transaction_data.close_price -
        row_stock_transaction_data.ma120)                                            / row_stock_transaction_data.ma120 * 100;
      ELSE
        v_bias120 := NULL;
      END IF;
      IF row_stock_transaction_data.ma250 IS NOT NULL THEN
        v_bias250                         := (row_stock_transaction_data.close_price -
        row_stock_transaction_data.ma250)                                            / row_stock_transaction_data.ma250 * 100;
      ELSE
        v_bias250 := NULL;
      END IF;
      -- 更新某个股票在某一日的5日乖离率、10日乖离率、20日乖离率、60日乖离率、120日乖离率、250日乖离率
      UPDATE stock_transaction_data t
      SET t.bias5   = v_bias5,
        t.bias10    = v_bias10,
        t.bias20    = v_bias20,
        t.bias60    = v_bias60,
        t.bias120   = v_bias120,
        t.bias250   = v_bias250
      WHERE t.code_ = v_code
      AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
    END LOOP;
    CLOSE cur_single_stock;
    COMMIT;
  END LOOP;
  CLOSE cur_all_stock_code;
END WRITE_BIAS_BY_DATE;
/*------------------------------ 计算所有股票的方差 -----------------------------------------*/
PROCEDURE WRITE_VARIANCE
IS
  -- 股票code
  v_code VARCHAR2(10);
  -- 股票date
  v_date DATE;
  -- 方差
  v_variance5   NUMBER;
  v_variance10  NUMBER;
  v_variance20  NUMBER;
  v_variance60  NUMBER;
  v_variance120 NUMBER;
  v_variance250 NUMBER;
  -- 计算方差时用到的变量
  v_sum5   NUMBER;
  v_sum10  NUMBER;
  v_sum20  NUMBER;
  v_sum60  NUMBER;
  v_sum120 NUMBER;
  v_sum250 NUMBER;
  -- 均线
  v_ma5   NUMBER;
  v_ma10  NUMBER;
  v_ma20  NUMBER;
  v_ma60  NUMBER;
  v_ma120 NUMBER;
  v_ma250 NUMBER;
  -- 需要计算的记录数
  v_rownum NUMBER;
  -- 所有股票的code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_ FROM stock_transaction_data t;
  -- 某一只股票的全部交易日期，并降序排列
  CURSOR cur_std_by_code
  IS
    SELECT *
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    ORDER BY t.date_ DESC;
  -- 某只股票、在某个日期之前的全部记录，按日期降序排列
  CURSOR cur_std_by_code_date
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_  <= v_date
      ORDER BY t.date_ DESC
      )
  WHERE rownum <= v_rownum;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    FOR j IN cur_std_by_code
    LOOP
      v_date  := j.date_;
      v_ma5   := j.ma5;
      v_ma10  := j.ma10;
      v_ma20  := j.ma20;
      v_ma60  := j.ma60;
      v_ma120 := j.ma120;
      v_ma250 := j.ma250;
      -- 重置
      v_variance5   := NULL;
      v_variance10  := NULL;
      v_variance20  := NULL;
      v_variance60  := NULL;
      v_variance120 := NULL;
      v_variance250 := NULL;
      v_sum5        := 0;
      v_sum10       := 0;
      v_sum20       := 0;
      v_sum60       := 0;
      v_sum120      := 0;
      v_sum250      := 0;
      -- 计算variance5
      IF v_ma5   IS NOT NULL THEN
        v_rownum := 5;
        FOR x IN cur_std_by_code_date
        LOOP
          v_sum5 := v_sum5 + power(x.close_price - v_ma5, 2);
        END LOOP;
        v_variance5 := 1 / (v_rownum - 1) * v_sum5;
      END IF;
      -- 计算variance10
      IF v_ma10  IS NOT NULL THEN
        v_rownum := 10;
        FOR x IN cur_std_by_code_date
        LOOP
          v_sum10 := v_sum10 + power(x.close_price - v_ma10, 2);
        END LOOP;
        v_variance10 := 1 / (v_rownum - 1) * v_sum10;
      END IF;
      -- 计算variance20
      IF v_ma20  IS NOT NULL THEN
        v_rownum := 20;
        FOR x IN cur_std_by_code_date
        LOOP
          v_sum20 := v_sum20 + power(x.close_price - v_ma20, 2);
        END LOOP;
        v_variance20 := 1 / (v_rownum - 1) * v_sum20;
      END IF;
      -- 计算variance60
      IF v_ma60  IS NOT NULL THEN
        v_rownum := 60;
        FOR x IN cur_std_by_code_date
        LOOP
          v_sum60 := v_sum60 + power(x.close_price - v_ma60, 2);
        END LOOP;
        v_variance60 := 1 / (v_rownum - 1) * v_sum60;
      END IF;
      -- 计算variance120
      IF v_ma120 IS NOT NULL THEN
        v_rownum := 120;
        FOR x IN cur_std_by_code_date
        LOOP
          v_sum120 := v_sum120 + power(x.close_price - v_ma120, 2);
        END LOOP;
        v_variance120 := 1 / (v_rownum - 1) * v_sum120;
      END IF;
      -- 计算variance250
      IF v_ma250 IS NOT NULL THEN
        v_rownum := 250;
        FOR x IN cur_std_by_code_date
        LOOP
          v_sum250 := v_sum250 + power(x.close_price - v_ma250, 2);
        END LOOP;
        v_variance250 := 1 / (v_rownum - 1) * v_sum250;
      END IF;
      -- 更新
      UPDATE stock_transaction_data t
      SET t.variance5 = v_variance5,
        t.variance10  = v_variance10,
        t.variance20  = v_variance20,
        t.variance60  = v_variance60,
        t.variance120 = v_variance120,
        t.variance250 = v_variance250
      WHERE t.code_   = v_code
      AND t.date_     = v_date;
    END LOOP;
  END LOOP;
  COMMIT;
END WRITE_VARIANCE;
/*------------------------------ 按照日期，计算所有股票在某一日的的方差 -----------------------------------------*/
PROCEDURE WRITE_VARIANCE_BY_DATE(
    p_date IN VARCHAR2)
IS
  -- 股票code
  v_code VARCHAR2(10);
  -- 股票date
  --v_date date;
  -- 方差
  v_variance5   NUMBER;
  v_variance10  NUMBER;
  v_variance20  NUMBER;
  v_variance60  NUMBER;
  v_variance120 NUMBER;
  v_variance250 NUMBER;
  -- 计算方差时用到的变量
  v_sum5   NUMBER;
  v_sum10  NUMBER;
  v_sum20  NUMBER;
  v_sum60  NUMBER;
  v_sum120 NUMBER;
  v_sum250 NUMBER;
  -- 均线
  v_ma5   NUMBER;
  v_ma10  NUMBER;
  v_ma20  NUMBER;
  v_ma60  NUMBER;
  v_ma120 NUMBER;
  v_ma250 NUMBER;
  -- 需要计算的记录数
  v_rownum NUMBER;
  -- stock_transaction_data类型的变量
  row_stock_transaction_data stock_transaction_data%rowtype;
  -- 所有股票的code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_ FROM stock_transaction_data t;
  -- 某只股票、在某个日期之前的全部记录，按日期降序排列
  CURSOR cur_std_by_code_date
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
      ORDER BY t.date_ DESC
      )
  WHERE rownum <= v_rownum;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    BEGIN
      SELECT *
      INTO row_stock_transaction_data
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('STOCK_TRANSACTION_DATA表中，没有code_为【' || v_code || '】，date_为【' || p_date || '】时的记录');
      CONTINUE;
    END;
    v_ma5   := row_stock_transaction_data.ma5;
    v_ma10  := row_stock_transaction_data.ma10;
    v_ma20  := row_stock_transaction_data.ma20;
    v_ma60  := row_stock_transaction_data.ma60;
    v_ma120 := row_stock_transaction_data.ma120;
    v_ma250 := row_stock_transaction_data.ma250;
    -- 重置
    v_variance5   := NULL;
    v_variance10  := NULL;
    v_variance20  := NULL;
    v_variance60  := NULL;
    v_variance120 := NULL;
    v_variance250 := NULL;
    v_sum5        := 0;
    v_sum10       := 0;
    v_sum20       := 0;
    v_sum60       := 0;
    v_sum120      := 0;
    v_sum250      := 0;
    -- 计算variance5
    IF v_ma5   IS NOT NULL THEN
      v_rownum := 5;
      FOR x IN cur_std_by_code_date
      LOOP
        v_sum5 := v_sum5 + power(x.close_price - v_ma5, 2);
      END LOOP;
      v_variance5 := 1 / (v_rownum - 1) * v_sum5;
    END IF;
    -- 计算variance10
    IF v_ma10  IS NOT NULL THEN
      v_rownum := 10;
      FOR x IN cur_std_by_code_date
      LOOP
        v_sum10 := v_sum10 + power(x.close_price - v_ma10, 2);
      END LOOP;
      v_variance10 := 1 / (v_rownum - 1) * v_sum10;
    END IF;
    -- 计算variance20
    IF v_ma20  IS NOT NULL THEN
      v_rownum := 20;
      FOR x IN cur_std_by_code_date
      LOOP
        v_sum20 := v_sum20 + power(x.close_price - v_ma20, 2);
      END LOOP;
      v_variance20 := 1 / (v_rownum - 1) * v_sum20;
    END IF;
    -- 计算variance60
    IF v_ma60  IS NOT NULL THEN
      v_rownum := 60;
      FOR x IN cur_std_by_code_date
      LOOP
        v_sum60 := v_sum60 + power(x.close_price - v_ma60, 2);
      END LOOP;
      v_variance60 := 1 / (v_rownum - 1) * v_sum60;
    END IF;
    -- 计算variance120
    IF v_ma120 IS NOT NULL THEN
      v_rownum := 120;
      FOR x IN cur_std_by_code_date
      LOOP
        v_sum120 := v_sum120 + power(x.close_price - v_ma120, 2);
      END LOOP;
      v_variance120 := 1 / (v_rownum - 1) * v_sum120;
    END IF;
    -- 计算variance250
    IF v_ma250 IS NOT NULL THEN
      v_rownum := 250;
      FOR x IN cur_std_by_code_date
      LOOP
        v_sum250 := v_sum250 + power(x.close_price - v_ma250, 2);
      END LOOP;
      v_variance250 := 1 / (v_rownum - 1) * v_sum250;
    END IF;
    -- 更新
    UPDATE stock_transaction_data t
    SET t.variance5 = v_variance5,
      t.variance10  = v_variance10,
      t.variance20  = v_variance20,
      t.variance60  = v_variance60,
      t.variance120 = v_variance120,
      t.variance250 = v_variance250
    WHERE t.code_   = v_code
    AND t.date_     = to_date(p_date, 'yyyy-mm-dd');
  END LOOP;
  COMMIT;
END WRITE_VARIANCE_BY_DATE;
/*------------------------------ judge if MACD of the stock is the gold fork by date -----------------------------------------*/
PROCEDURE SELECT_MACD_UP_BELOW_ZERO(
    p_date IN VARCHAR2,
    p_rate IN NUMBER,
    p_stock_macd_result OUT T_TYPE_ARRAY)
AS
  -- 定义一个stock_transaction_data%rowtype类型的变量
  row_stock_record stock_transaction_data%rowtype;
  -- 定义一个含有5个数值型数据的数组
type type_array IS varray(3) OF stock_transaction_data%rowtype;
array_dif_dea type_array := type_array();
-- 表示code_字段
v_code VARCHAR2(10);
-- define cursor section.返回全部code_字段
CURSOR cur_all_stock_code
IS
  SELECT DISTINCT std.code_
  FROM stock_transaction_data std
  ORDER BY std.code_ ASC;
-- 获取某只股票某一日及之前一日的记录
CURSOR cur_last_two_day_stock_info
IS
  SELECT *
  FROM
    (SELECT *
    FROM stock_transaction_data std
    WHERE std.code_ = v_code
    AND std.date_  <= to_date(p_date, 'yyyy-mm-dd')
    ORDER BY std.date_ DESC
    )
WHERE rownum <= 2;
-- 创建UTL_FILE.file_type对象，用于读写文件
file_handle UTL_FILE.file_type;
BEGIN
  p_stock_macd_result := T_TYPE_ARRAY();
  file_handle         := UTL_FILE.FOPEN('TXTDIR', 'SELECT_MACD_UP_BELOW_ZERO.txt', 'w');
  FOR i IN cur_all_stock_code
  LOOP
    v_code        := i.code_;
    array_dif_dea := type_array();
    FOR j IN cur_last_two_day_stock_info
    LOOP
      row_stock_record.dif   := j.dif;
      row_stock_record.dea   := j.dea;
      row_stock_record.code_ := j.code_;
      array_dif_dea.extend;
      array_dif_dea(array_dif_dea.count) := row_stock_record;
    END LOOP;
    -- 由于有些股票是在p_date之后才上市交易，因此没有记录，即array_dif_dea.count!=2，所以此处要先判断一下
    IF array_dif_dea.count                                                                                  = 2 THEN
      IF array_dif_dea(1).dif                                                                               > array_dif_dea(1).dea AND array_dif_dea(2) .dif < array_dif_dea(2).dea THEN
        IF (array_dif_dea(1) .dif + array_dif_dea(1).dea + array_dif_dea(2).dif + array_dif_dea(2).dea) / 4 < p_rate THEN
          dbms_output.put_line(array_dif_dea(1).code_);
          p_stock_macd_result.extend;
          p_stock_macd_result(p_stock_macd_result.count) := array_dif_dea(1) .code_;
          UTL_FILE.PUT_LINE(file_handle, array_dif_dea(1).code_);
        END IF;
      END IF;
    END IF;
  END LOOP;
  UTL_FILE.FCLOSE(file_handle);
END SELECT_MACD_UP_BELOW_ZERO;
/*-------------------------------- calculate WRITE_CHANGE_RANGE_EX_RIGHT----------------------------------------*/
PROCEDURE WRITE_CHANGE_RANGE_EX_RIGHT
AS
  -- 表示股票代码
  v_code VARCHAR2(10);
  -- 用来记录某只股票的第一条记录
  row_stock_transaction_data stock_transaction_data%rowtype;
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_
    FROM stock_transaction_data_all t
    ORDER BY t.code_ ASC;
  -- 返回某一只股票除第一条记录以外的其他记录，并按生序排列
  CURSOR cur_single_stock_other_record
  IS
    SELECT *
    FROM stock_transaction_data_all t
    WHERE t.code_ = v_code
    AND t.date_  != row_stock_transaction_data.date_
    ORDER BY t.date_ ASC;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- 计算某一只股票change_range_ex_right字段的值
    SELECT *
    INTO row_stock_transaction_data
    FROM
      (SELECT *
      FROM stock_transaction_data_all t
      WHERE t.code_ = i.code_
      ORDER BY t.date_ ASC
      )
    WHERE rownum <= 1;
    FOR j IN cur_single_stock_other_record
    LOOP
      UPDATE stock_transaction_data t
      SET t.change_range_ex_right             = ROUND((j.close_price - row_stock_transaction_data.close_price) / row_stock_transaction_data.close_price, 4) * 100
      WHERE t.code_                           = i.code_
      AND t.date_                             = j.date_;
      row_stock_transaction_data.close_price := j.close_price;
      COMMIT;
    END LOOP;
  END LOOP;
END WRITE_CHANGE_RANGE_EX_RIGHT;
/*-------------------------------- calculate WRITE_CHANGE_RANGE_EX_RIGHT_BY_DATE----------------------------------------*/
PROCEDURE WRITE_C_R_E_R_BY_DATE(
    p_date IN VARCHAR2)
AS
  -- 表示股票代码
  v_code VARCHAR2(10);
  -- 当日的收盘价
  v_current_close_price NUMBER;
  -- 前一日收盘价
  v_last_close_price NUMBER;
  -- define cursor section.返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_
    FROM stock_transaction_data_all t
    ORDER BY t.code_ ASC;
BEGIN
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    -- 当日的收盘价
    BEGIN
      SELECT t.close_price
      INTO v_current_close_price
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('STOCK_TRANSACTION_DATA表中，没有code_为【' || v_code || '】，date_为【' || p_date || '】时的记录');
      CONTINUE;
    END;
    -- 前一日收盘价
    BEGIN
      SELECT b.close_price
      INTO v_last_close_price
      FROM
        (SELECT *
        FROM stock_transaction_data t
        WHERE t.code_ = v_code
        AND t.date_   < to_date(p_date, 'yyyy-mm-dd')
        ORDER BY t.date_ DESC
        ) b
      WHERE rownum <= 1;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('STOCK_TRANSACTION_DATA表中，没有code_为【' || v_code || '】，date_为【' || p_date || '】时的记录');
      CONTINUE;
    END;
    -- 更新change_range_ex_right字段
    UPDATE stock_transaction_data
    SET change_range_ex_right = ROUND((v_current_close_price - v_last_close_price) / v_last_close_price, 4) * 100
    WHERE code_               = i.code_
    AND date_                 = to_date(p_date, 'yyyy-mm-dd');
  END LOOP;
  COMMIT;
END WRITE_C_R_E_R_BY_DATE;
/*--------------------- 计算stock_transaction_data_all表中的布林带 -----------------------*/
PROCEDURE CAL_BOLL
IS
  -- 股票代码
  v_code VARCHAR(50);
  -- 布林带中轨、上轨、下轨
  v_mb NUMBER;
  v_up NUMBER;
  v_dn NUMBER;
  -- 计算标准差时使用
  v_sum NUMBER := 0;
  -- 记录数量
  v_num NUMBER;
  -- 某只股票的一条交易记录
  row_stock stock_transaction_data_all%rowtype;
  row_stock_desc stock_transaction_data_all%rowtype;
  -- 周线级别所有股票的code_
  CURSOR cur_stock_code
  IS
    SELECT DISTINCT t.code_ FROM stock_transaction_data t;
  -- 某一只股票的全部交易记录，升序排列
  CURSOR cur_single_stock
  IS
    SELECT *
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    AND t.ma20   IS NOT NULL
    ORDER BY t.date_ ASC;
  -- 某只股票，在某日之后的交易记录，降序排列
  CURSOR cur_stock_desc
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = row_stock.code_
      AND t.date_  <= row_stock.date_
      ORDER BY t.date_ DESC
      )
  WHERE rownum <= 20;
BEGIN
  OPEN cur_stock_code;
  LOOP
    FETCH cur_stock_code INTO v_code;
    EXIT
  WHEN cur_stock_code%notfound;
    OPEN cur_single_stock;
    LOOP
      FETCH cur_single_stock INTO row_stock;
      EXIT
    WHEN cur_single_stock%notfound;
      -- 初始化
      v_up  := 0;
      v_dn  := 0;
      v_sum := 0;
      -- 中轨
      SELECT AVG(t1.close_price)
      INTO v_mb
      FROM
        (SELECT *
        FROM stock_transaction_data t
        WHERE t.code_ = row_stock.code_
        AND t.date_  <= row_stock.date_
        ORDER BY t.date_ DESC
        ) t1
      WHERE rownum <= 20;
      -- v_mb := row_stock.ma20;
      -- 如果交易次数不够20，则返回
      SELECT COUNT(*)
      INTO v_num
      FROM stock_transaction_data t
      WHERE t.code_ = row_stock.code_
      AND t.date_  <= row_stock.date_;
      IF v_num      < 20 THEN
        CONTINUE;
      END IF;
      OPEN cur_stock_desc;
      LOOP
        FETCH cur_stock_desc INTO row_stock_desc;
        EXIT
      WHEN cur_stock_desc%notfound;
        v_sum := v_sum + power(row_stock_desc.close_price - v_mb, 2);
      END LOOP;
      CLOSE cur_stock_desc;
      -- 上轨、下轨
      v_up := v_mb + 2 * sqrt(v_sum / 20);
      v_dn := v_mb - 2 * sqrt(v_sum / 20);
      -- 更新记录
      UPDATE stock_transaction_data
      SET mb      = v_mb,
        up        = v_up,
        dn_       = v_dn
      WHERE code_ = row_stock.code_
      AND date_   = row_stock.date_;
    END LOOP;
    CLOSE cur_single_stock;
  END LOOP;
  CLOSE cur_stock_code;
  COMMIT;
END CAL_BOLL;
/*------- 计算某一日stock_transaction_data_all表中的布林带，必须在计算完均线后运行 ------*/
PROCEDURE CAL_BOLL_BY_DATE(
    p_date VARCHAR2)
IS
  -- 股票代码
  v_code VARCHAR(50);
  -- 布林带中轨、上轨、下轨
  v_mb NUMBER;
  v_up NUMBER;
  v_dn NUMBER;
  -- 计算标准差时使用
  v_sum NUMBER := 0;
  -- 记录数量
  v_num NUMBER;
  -- 某只股票的一条交易记录
  row_stock_desc stock_transaction_data%rowtype;
  -- 股票的code_
  CURSOR cur_stock_code
  IS
    SELECT DISTINCT t.code_
    FROM stock_transaction_data t
    WHERE t.date_ = to_date(p_date, 'yyyy-mm-dd');
  -- 某一只股票的全部交易记录，降序排列
  /*cursor cur_single_stock is
  select *
  from stock_transaction_data t
  where t.code_ = v_code
  and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
  and t.ma20 is not null
  order by t.date_ desc;*/
  -- 某只股票，在某日之后的交易记录，降序排列
  CURSOR cur_stock_desc
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
      ORDER BY t.date_ DESC
      )
  WHERE rownum <= 20;
BEGIN
  OPEN cur_stock_code;
  LOOP
    FETCH cur_stock_code INTO v_code;
    EXIT
  WHEN cur_stock_code%notfound;
    /*open cur_single_stock;
    loop
    fetch cur_single_stock
    into row_stock;
    exit when cur_single_stock%notfound;*/
    -- 如果交易次数不够20，则返回
    SELECT COUNT(*)
    INTO v_num
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    AND t.date_  <= to_date(p_date, 'yyyy-mm-dd');
    IF v_num      < 20 THEN
      CONTINUE;
    END IF;
    -- 初始化
    v_up  := 0;
    v_dn  := 0;
    v_sum := 0;
    -- 中轨
    SELECT AVG(t1.close_price)
    INTO v_mb
    FROM
      (SELECT *
      FROM stock_transaction_data t
      WHERE t.code_ = v_code
      AND t.date_  <= to_date(p_date, 'yyyy-mm-dd')
      ORDER BY t.date_ DESC
      ) t1
    WHERE rownum <= 20;
    -- v_mb := row_stock.ma20;
    OPEN cur_stock_desc;
    LOOP
      FETCH cur_stock_desc INTO row_stock_desc;
      EXIT
    WHEN cur_stock_desc%notfound;
      v_sum := v_sum + power(row_stock_desc.close_price - v_mb, 2);
    END LOOP;
    CLOSE cur_stock_desc;
    -- 上轨、下轨
    v_up := v_mb + 2 * sqrt(v_sum / 20);
    v_dn := v_mb - 2 * sqrt(v_sum / 20);
    -- 更新记录
    UPDATE stock_transaction_data
    SET mb      = v_mb,
      up        = v_up,
      dn_       = v_dn
    WHERE code_ = v_code
    AND date_   = to_date(p_date, 'yyyy-mm-dd');
    /*end loop;
    close cur_single_stock;*/
  END LOOP;
  CLOSE cur_stock_code;
  COMMIT;
END CAL_BOLL_BY_DATE;
/*----------------- calculate the volatility of stock close price with five day,ten day and twenty day -----------------------*/
PROCEDURE CALCULATE_VOLATILITY
AS
  -- 表示stock_code
  stockCode VARCHAR2(10);
  -- 表示stock_date
  stockDate DATE;
  -- 表示五日内波动的总数
  fiveDayVolatilitySum NUMBER;
  -- 表示十日内波动的总数
  tenDayVolatilitySum NUMBER;
  -- 表示二十五日内波动的总数
  twentyDayVolatilitySum NUMBER;
  -- 计算五日内波动时的前一条记录
  initFiveDayStockRecord stock_moving_average%rowtype;
  -- 计算十日内波动时的前一条记录
  initTenDayStockRecord stock_moving_average%rowtype;
  -- 计算二十日内波动时的前一条记录
  initTwentyDayStockRecord stock_moving_average%rowtype;
  -- 返回全部stock_code
  CURSOR allStockCode
  IS
    SELECT DISTINCT t.stock_code
    FROM stock_moving_average t
    ORDER BY t.stock_code ASC;
  -- 返回全部stock_date
  CURSOR allStockDate
  IS
    SELECT DISTINCT t.stock_date
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    ORDER BY t.stock_date DESC;
  -- 用于获取从指定日期开始五日内的记录，并按降序排列
  CURSOR nextFiveRecord
  IS
    SELECT *
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    AND t.stock_date   < initFiveDayStockRecord.stock_date
    AND rownum        <= 5
    ORDER BY t.stock_date DESC;
  -- 用于获取从指定日期开始十日内的记录，并按降序排列
  CURSOR nextTenRecord
  IS
    SELECT *
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    AND t.stock_date   < initTenDayStockRecord.stock_date
    AND rownum        <= 10
    ORDER BY t.stock_date DESC;
  -- 用于获取从指定日期开始二十日内的记录，并按降序排列
  CURSOR nextTwentyRecord
  IS
    SELECT *
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    AND t.stock_date   < initTwentyDayStockRecord.stock_date
    AND rownum        <= 20
    ORDER BY t.stock_date DESC;
BEGIN
  --- 计算字段five_day_volatility
  fiveDayVolatilitySum := 0;
  FOR i IN allStockCode
  LOOP
    stockCode := i.stock_code;
    FOR j IN allStockDate
    LOOP
      stockDate := j.stock_date;
      SELECT *
      INTO initFiveDayStockRecord
      FROM stock_moving_average t
      WHERE t.stock_code = stockCode
      AND t.stock_date  <= stockDate
      AND rownum        <= 1
      ORDER BY t.stock_date DESC;
      FOR x IN nextFiveRecord
      LOOP
        fiveDayVolatilitySum := fiveDayVolatilitySum + ABS((initFiveDayStockRecord.stock_close -
        x.stock_close)                               / x.stock_close);
        IF nextFiveRecord%ROWCOUNT = 5 THEN
          UPDATE stock_moving_average t
          SET t.five_day_volatility = ROUND(fiveDayVolatilitySum / 5, 4) * 100
          WHERE t.stock_code        = stockCode
          AND t.stock_date          = stockDate;
          COMMIT;
        END IF;
        initFiveDayStockRecord.stock_close := x.stock_close;
      END LOOP;
      fiveDayVolatilitySum := 0;
    END LOOP;
  END LOOP;
  --- 计算字段ten_day_volatility
  tenDayVolatilitySum := 0;
  FOR i IN allStockCode
  LOOP
    stockCode := i.stock_code;
    FOR j IN allStockDate
    LOOP
      stockDate := j.stock_date;
      SELECT *
      INTO initTenDayStockRecord
      FROM stock_moving_average t
      WHERE t.stock_code = stockCode
      AND t.stock_date  <= stockDate
      AND rownum        <= 1
      ORDER BY t.stock_date DESC;
      FOR x IN nextTenRecord
      LOOP
        tenDayVolatilitySum := tenDayVolatilitySum + ABS((initTenDayStockRecord.stock_close -
        x.stock_close)                             / x.stock_close);
        IF nextTenRecord%ROWCOUNT = 10 THEN
          UPDATE stock_moving_average t
          SET t.ten_day_volatility = ROUND(tenDayVolatilitySum / 10, 4) * 100
          WHERE t.stock_code       = stockCode
          AND t.stock_date         = stockDate;
          COMMIT;
        END IF;
        initTenDayStockRecord.stock_close := x.stock_close;
      END LOOP;
      tenDayVolatilitySum := 0;
    END LOOP;
  END LOOP;
  --- 计算字段twenty_day_volatility
  twentyDayVolatilitySum := 0;
  FOR i IN allStockCode
  LOOP
    stockCode := i.stock_code;
    FOR j IN allStockDate
    LOOP
      stockDate := j.stock_date;
      SELECT *
      INTO initTwentyDayStockRecord
      FROM stock_moving_average t
      WHERE t.stock_code = stockCode
      AND t.stock_date  <= stockDate
      AND rownum        <= 1
      ORDER BY t.stock_date DESC;
      FOR x IN nextTwentyRecord
      LOOP
        twentyDayVolatilitySum := twentyDayVolatilitySum + ABS((initTwentyDayStockRecord.stock_close -
        x.stock_close)                                   / x.stock_close);
        IF nextTwentyRecord%ROWCOUNT = 20 THEN
          UPDATE stock_moving_average t
          SET t.twenty_day_volatility = ROUND(twentyDayVolatilitySum / 20, 4) * 100
          WHERE t.stock_code          = stockCode
          AND t.stock_date            = stockDate;
          COMMIT;
        END IF;
        initTwentyDayStockRecord.stock_close := x.stock_close;
      END LOOP;
      twentyDayVolatilitySum := 0;
    END LOOP;
  END LOOP;
END CALCULATE_VOLATILITY;
/*------------------------- calculate the correlation rate of a certain stock with several index -------------------------------*/
PROCEDURE CALCULATE_CORRELATION_RATE(
    stockCode IN VARCHAR2)
AS
  -- 用于写文件的变量
  fHandle utl_file.file_type;
  -- 表示上证指数，深证成指，中小板指数和创业板指数
  shIndexCode  CONSTANT VARCHAR2(8) := 'sh000001';
  szIndexCode  CONSTANT VARCHAR2(8) := 'sz399001';
  zxbIndexCode CONSTANT VARCHAR2(8) := 'sz399005';
  cybIndexCode CONSTANT VARCHAR2(8) := 'sz399006';
  -- 指定股票的最近10天的数据
  CURSOR lastFiveStockInfo
  IS
    SELECT *
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    AND rownum        <= 10
    ORDER BY t.stock_date DESC;
  lastFiveUpNumber NUMBER;
  -- 上证指数，深证成指，中小板指数和创业板指数的最近10天的数据
  CURSOR lastFiveShIndexInfo
  IS
    SELECT *
    FROM stock_index t
    WHERE t.code_ = shIndexCode
    AND rownum   <= 10
    ORDER BY t.date_ DESC;
  CURSOR lastFiveSzIndexInfo
  IS
    SELECT *
    FROM stock_index t
    WHERE t.code_ = szIndexCode
    AND rownum   <= 10
    ORDER BY t.date_ DESC;
  CURSOR lastFiveZxbIndexInfo
  IS
    SELECT *
    FROM stock_index t
    WHERE t.code_ = zxbIndexCode
    AND rownum   <= 10
    ORDER BY t.date_ DESC;
  CURSOR lastFiveCybIndexInfo
  IS
    SELECT *
    FROM stock_index t
    WHERE t.code_ = cybIndexCode
    AND rownum   <= 10
    ORDER BY t.date_ DESC;
BEGIN
  FOR i IN lastFiveStockInfo
  LOOP
    IF i.up_down        = 1 THEN
      lastFiveUpNumber := lastFiveUpNumber + 1;
    END IF;
  END LOOP;
END CALCULATE_CORRELATION_RATE;
/*------------------------------------------------ 计算某一日所有股票的涨跌幅度 ------------------------------------------------*/
PROCEDURE CAL_UP_DOWN_PERCENTAGE_BY_DATE(
    stockDate IN VARCHAR2)
AS
  -- 表示stock_code
  stockCode VARCHAR2(10);
  -- define cursor section.返回全部stock_code
  CURSOR allStockCode
  IS
    SELECT DISTINCT t.stock_code
    FROM stock_moving_average t
    ORDER BY t.stock_code ASC;
  -- 表示某一只股票某一天的记录
  CURSOR singleStockByDate
  IS
    SELECT *
    FROM stock_moving_average t
    WHERE t.stock_date = to_date(stockDate, 'yyyy-mm-dd')
    AND t.stock_code   = stockCode;
  -- 表示某一只股票某一天之前的一天的记录
  CURSOR singleStockLastDate
  IS
    SELECT *
    FROM stock_moving_average t
    WHERE t.stock_date < to_date(stockDate, 'yyyy-mm-dd')
    AND t.stock_code   = stockCode
    AND rownum        <= 1
    ORDER BY t.stock_date DESC;
BEGIN
  FOR i IN allStockCode
  LOOP
    stockCode := i.stock_code;
    FOR j IN singleStockByDate
    LOOP
      FOR x IN singleStockLastDate
      LOOP
        UPDATE stock_moving_average t
        SET t.today_up_down_percentage = ROUND((j.stock_close - x.stock_close) / x.stock_close, 4) * 100
        WHERE t.stock_code             = stockCode
        AND t.stock_date               = to_date(stockDate, 'yyyy-mm-dd');
        COMMIT;
      END LOOP;
    END LOOP;
  END LOOP;
END CAL_UP_DOWN_PERCENTAGE_BY_DATE;
/*------------ 计算某一日所有股票价格的 -----------*/
PROCEDURE CAL_VOLATILITY_BY_DATE(
    stockDate IN VARCHAR2)
AS
  -- 表示stock_code
  stockCode VARCHAR2(10);
  -- 表示五日内波动的总数
  fiveDayVolatilitySum NUMBER;
  -- 表示十日内波动的总数
  tenDayVolatilitySum NUMBER;
  -- 表示二十五日内波动的总数
  twentyDayVolatilitySum NUMBER;
  -- 计算五日内波动时的前一条记录
  initFiveDayStockRecord stock_moving_average%rowtype;
  -- 计算十日内波动时的前一条记录
  initTenDayStockRecord stock_moving_average%rowtype;
  -- 计算二十日内波动时的前一条记录
  initTwentyDayStockRecord stock_moving_average%rowtype;
  --  由于存在停牌的股票，所以stockCount(用于记录某一日，某一只股票的数量)等于0时，表示该停牌
  stockCount NUMBER;
  -- 返回全部stock_code
  CURSOR allStockCode
  IS
    SELECT DISTINCT t.stock_code
    FROM stock_moving_average t
    ORDER BY t.stock_code ASC;
  -- 用于返回某日之后5天的记录（不包括某日）
  CURSOR lastFiveRecordByDate
  IS
    SELECT *
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    AND t.stock_date   < to_date(stockDate, 'yyyy-mm-dd')
    AND rownum        <= 5
    ORDER BY t.stock_date DESC;
  -- 用于返回某日之后10天的记录（不包括某日）
  CURSOR lastTenRecordByDate
  IS
    SELECT *
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    AND t.stock_date   < to_date(stockDate, 'yyyy-mm-dd')
    AND rownum        <= 10
    ORDER BY t.stock_date DESC;
  -- 用于返回某日之后20天的记录（不包括某日）
  CURSOR lastTwentyRecordByDate
  IS
    SELECT *
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    AND t.stock_date   < to_date(stockDate, 'yyyy-mm-dd')
    AND rownum        <= 20
    ORDER BY t.stock_date DESC;
BEGIN
  --- 计算字段five_day_volatility
  fiveDayVolatilitySum := 0;
  FOR i IN allStockCode
  LOOP
    stockCode := i.stock_code;
    SELECT COUNT(*)
    INTO stockCount
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    AND t.stock_date   = to_date(stockDate, 'yyyy-mm-dd');
    IF stockCount     <> 0 THEN
      SELECT *
      INTO initFiveDayStockRecord
      FROM stock_moving_average t
      WHERE t.stock_code = stockCode
      AND t.stock_date   = to_date(stockDate, 'yyyy-mm-dd');
      FOR j IN lastFiveRecordByDate
      LOOP
        fiveDayVolatilitySum := fiveDayVolatilitySum + ABS((initFiveDayStockRecord.stock_close -
        j.stock_close)                               / j.stock_close);
        IF lastFiveRecordByDate%ROWCOUNT = 5 THEN
          UPDATE stock_moving_average t
          SET t.five_day_volatility = ROUND(fiveDayVolatilitySum / 5, 4) * 100
          WHERE t.stock_code        = stockCode
          AND t.stock_date          = to_date(stockDate, 'yyyy-mm-dd');
          COMMIT;
        END IF;
        initFiveDayStockRecord.stock_close := j.stock_close;
      END LOOP;
      fiveDayVolatilitySum := 0;
    END IF;
  END LOOP;
  --- 计算字段ten_day_volatility
  tenDayVolatilitySum := 0;
  FOR i IN allStockCode
  LOOP
    stockCode := i.stock_code;
    SELECT COUNT(*)
    INTO stockCount
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    AND t.stock_date   = to_date(stockDate, 'yyyy-mm-dd');
    IF stockCount     <> 0 THEN
      SELECT *
      INTO initTenDayStockRecord
      FROM stock_moving_average t
      WHERE t.stock_code = stockCode
      AND t.stock_date   = to_date(stockDate, 'yyyy-mm-dd');
      FOR j IN lastTenRecordByDate
      LOOP
        tenDayVolatilitySum := tenDayVolatilitySum + ABS((initTenDayStockRecord.stock_close -
        j.stock_close)                             / j.stock_close);
        IF lastTenRecordByDate%ROWCOUNT = 10 THEN
          UPDATE stock_moving_average t
          SET t.ten_day_volatility = ROUND(tenDayVolatilitySum / 10, 4) * 100
          WHERE t.stock_code       = stockCode
          AND t.stock_date         = to_date(stockDate, 'yyyy-mm-dd');
          COMMIT;
        END IF;
        initTenDayStockRecord.stock_close := j.stock_close;
      END LOOP;
      tenDayVolatilitySum := 0;
    END IF;
  END LOOP;
  --- 计算字段twenty_day_volatility
  twentyDayVolatilitySum := 0;
  FOR i IN allStockCode
  LOOP
    stockCode := i.stock_code;
    SELECT COUNT(*)
    INTO stockCount
    FROM stock_moving_average t
    WHERE t.stock_code = stockCode
    AND t.stock_date   = to_date(stockDate, 'yyyy-mm-dd');
    IF stockCount     <> 0 THEN
      SELECT *
      INTO initTwentyDayStockRecord
      FROM stock_moving_average t
      WHERE t.stock_code = stockCode
      AND t.stock_date   = to_date(stockDate, 'yyyy-mm-dd');
      FOR j IN lastTwentyRecordByDate
      LOOP
        twentyDayVolatilitySum := twentyDayVolatilitySum + ABS((initTwentyDayStockRecord.stock_close -
        j.stock_close)                                   / j.stock_close);
        IF lastTwentyRecordByDate%ROWCOUNT = 20 THEN
          UPDATE stock_moving_average t
          SET t.twenty_day_volatility = ROUND(twentyDayVolatilitySum / 20, 4) * 100
          WHERE t.stock_code          = stockCode
          AND t.stock_date            = to_date(stockDate, 'yyyy-mm-dd');
          COMMIT;
        END IF;
        initTwentyDayStockRecord.stock_close := j.stock_close;
      END LOOP;
      twentyDayVolatilitySum := 0;
    END IF;
  END LOOP;
END CAL_VOLATILITY_BY_DATE;
/*-------------------------------------------------- 查找某一日价格出现异动的股票 -----------------------------------------------*/
PROCEDURE FIND_ABNORMAL_STOCK(
    p_date IN VARCHAR2,
    p_rate IN NUMBER,
    p_stock_result_array OUT T_STOCK_RESULT_ARRAY)
AS
  -- 表示code_
  v_code VARCHAR2(10);
  -- T_STOCK_RESULT类型对象。表示出现异动的股票
  type_stock_result T_STOCK_RESULT;
  -- 返回全部stock_code
  CURSOR cur_all_stock_code
  IS
    SELECT DISTINCT t.code_
    FROM stock_transaction_data t
      /*where t.stock_code='sh600094'*/
    ORDER BY t.code_ ASC;
  -- 某一只股票，某一天的记录
  CURSOR cur_single_stock_record
  IS
    SELECT *
    FROM stock_transaction_data t
    WHERE t.code_ = v_code
    AND t.date_   = to_date(p_date, 'yyyy-mm-dd');
  -- 创建UTL_FILE.file_type对象，用于读写文件
  file_handle UTL_FILE.file_type;
BEGIN
  file_handle          := UTL_FILE.FOPEN('TXTDIR', 'FIND_ABNORMAL_STOCK.txt', 'w');
  p_stock_result_array := T_STOCK_RESULT_ARRAY();
  FOR i IN cur_all_stock_code
  LOOP
    v_code := i.code_;
    FOR j IN cur_single_stock_record
    LOOP
      IF cur_single_stock_record%FOUND THEN
        IF j.change_range        IS NOT NULL AND j.five_day_volatility IS NOT NULL AND j.ten_day_volatility IS NOT NULL AND j.twenty_day_volatility IS NOT NULL THEN
          IF ABS(j.change_range) >= j.five_day_volatility * p_rate AND ABS(j.change_range) >= j.ten_day_volatility * p_rate AND ABS(j.change_range) >= j.twenty_day_volatility * p_rate THEN
            dbms_output.put_line(j.code_ || '   ' || j.date_);
            type_stock_result := T_STOCK_RESULT(j.code_, j.date_);
            p_stock_result_array.extend;
            p_stock_result_array(p_stock_result_array.count) := type_stock_result;
            UTL_FILE.PUT_LINE(file_handle, p_stock_result_array(p_stock_result_array.count) .get_code() || '   ' || p_stock_result_array(p_stock_result_array.count) .get_date());
          END IF;
        END IF;
      END IF;
    END LOOP;
  END LOOP;
END FIND_ABNORMAL_STOCK;
/*----------------------------------------------- 查找某个交易日MACD最小的股票 -----------------------------------------*/
PROCEDURE FIND_LOWEST_MACD_STOCK(
    p_stock_date    IN VARCHAR2,
    p_stock_number  IN NUMBER,
    p_interval_date IN NUMBER,
    p_stock_result_array OUT t_stock_result_array)
AS
  -- 定义数组，用于接收游标的结果集，字段为v_stock_code和v_dif_dea
type typ_result
IS
  record
  (
    v_stock_code VARCHAR2(20),
    v_dif_dea    NUMBER);
type typ_results IS varray(1000) OF typ_result;
results typ_results;
CN_BATCH_SIZE_CONSTANT pls_integer := 1000;
-- 表示方法FNC_STOCK_XR返回的结果
v_fnc_result NUMBER := 0;
-- 表示开始日期
v_begin_date DATE;
-- 表示T_STOCK_RESULT类型
v_stock_result T_STOCK_RESULT;
-- 表示需要返回的股票的数量
v_stock_number NUMBER := 0;
-- 获取某个交易日，MACD按照升序排列的股票
cur_lowest_macd_stock sys_refcursor;
vc_lowest_macd_stock VARCHAR2(2000) := 'select t.code_, (t.dif + t.dea) / 2 dif_dea                                            
from stock_transaction_data t                                           
where t.date_ = to_date(:1, ''yyyy-mm-dd'')                                           
order by (t.dif + t.dea) / 2 asc';
-- 创建UTL_FILE.file_type对象，用于读写文件
file_handle UTL_FILE.file_type := UTL_FILE.FOPEN('TXTDIR', 'FIND_LOWEST_MACD_STOCK.txt', 'w');
BEGIN
  -- 初始化
  p_stock_result_array := T_STOCK_RESULT_ARRAY();
  -- 将当前session缓存cursor的数量设置为200
  EXECUTE immediate 'alter session set session_cached_cursors=200';
  OPEN cur_lowest_macd_stock FOR vc_lowest_macd_stock USING p_stock_date;
  LOOP
    FETCH cur_lowest_macd_stock bulk collect
    INTO results limit CN_BATCH_SIZE_CONSTANT;
    FOR i IN 1 .. results.count
    LOOP
      -- 求某只股票从p_stock_date开始向前p_interval_date个交易日的日期
      EXECUTE immediate 'select t2.date_                              
from (select t3.date_                                      
from (                                           
select * from (                                             
select *                                              
from stock_transaction_data t                                             
where t.code_ = :1                                             
order by t.date_ desc                                            
) t1 where rownum <= :2) t3                                     
order by t3.date_ asc) t2                             
where rownum <= 1' INTO v_begin_date USING results(i).v_stock_code,
      p_interval_date;
      -- 如果v_fnc_result为-1，则表示没有除权
      v_fnc_result     := FNC_STOCK_XR(results(i).v_stock_code, TO_CHAR(v_begin_date, 'yyyy-mm-dd'), p_stock_date);
      IF v_fnc_result   = -1 AND v_stock_number < p_stock_number THEN
        v_stock_result := T_STOCK_RESULT(results(i).v_stock_code || '   ' || results(i) .v_dif_dea, to_date(p_stock_date, 'yyyy-mm-dd'));
        p_stock_result_array.extend;
        p_stock_result_array(p_stock_result_array.count) := v_stock_result;
        v_stock_number                                   := v_stock_number + 1;
        UTL_FILE.PUT_LINE(file_handle, p_stock_result_array(p_stock_result_array.count) .get_code() || '   ' || p_stock_result_array(p_stock_result_array.count) .get_date());
      END IF;
    END LOOP;
    EXIT
  WHEN results.count < CN_BATCH_SIZE_CONSTANT;
  END LOOP;
END FIND_LOWEST_MACD_STOCK;
/*---------------------------------------------- 查找某段时间内涨幅最大的num只股票 ----------------------------------------*/
PROCEDURE FIND_TOP_STOCK(
    p_begin_date IN VARCHAR2,
    p_end_date   IN VARCHAR2,
    p_number     IN NUMBER,
    p_word_limit_num NUMBER,
    stock_top_result_array OUT T_STOCK_TOP_RESULT_ARRAY)
AS
  -- 创建UTL_FILE.file_type对象，用于读写文件
  file_handle UTL_FILE.file_type := UTL_FILE.FOPEN('TXTDIR', 'FIND_TOP_STOCK.txt', 'w');
  -- 查找beginDate至endDate中，按股票的涨幅进行排列，显示STOCK_CODE和涨幅百分比
  cur_top_stock sys_refcursor;
  vc_cur_top_stock VARCHAR2(1000) := 'select t.code_,         
round((max(t.close_price) - min(t.open_price)) /               
min(t.open_price) * 100,               
2) as rate         
from stock_transaction_data_all t         
where t.date_ between to_date(:1, ''yyyy-mm-dd'') and               
to_date(:2, ''yyyy-mm-dd'')         
group by t.code_         
order by rate desc';
  -- 定义类型typ_top_stock_rec和数组typ_top_stock_arr，用于存储从游标中获取的记录
type typ_top_stock_rec
IS
  record
  (
    stock_code stock_transaction_data.code_%type,
    rate NUMBER);
type typ_top_stock_arr IS varray(1000) OF typ_top_stock_rec;
top_stock_arr typ_top_stock_arr;
-- 用于限制每次fetch的记录数量
CN_BATCH_SIZE_CONSTANT pls_integer := 1000;
-- 表示stock_code字段
v_stock_code VARCHAR2(100);
-- 查询某只股票在某段时间内，最高价出现的那一天。注意：可能这个价格出现的不止一天，因此只取最早的一天
vc_max_high_date VARCHAR2(1000) := 'select distinct t1.date_      
from stock_transaction_data t1     
where rownum <= 1       
and t1.close_price =           
(select max(t.close_price)              
from stock_transaction_data_all t             
where t.date_ between                   
to_date(:1, ''yyyy-mm-dd'') and                   
to_date(:2, ''yyyy-mm-dd'')               
and t.code_ = :3)       
and t1.date_ between to_date(:4, ''yyyy-mm-dd'') and           
to_date(:5, ''yyyy-mm-dd'')       
and t1.code_ = :6     
order by t1.date_ asc';
-- 表示某只股票在某个时间内，最高价出现的那一天
v_stock_high_date DATE;
-- 查询某只股票在某段时间内，最低价出现的那一天。注意：可能这个价格出现的不止一天，因此只取最晚的一天
vc_min_low_date VARCHAR2(1000) := 'select distinct t1.date_        
from stock_transaction_data_all t1       
where rownum <= 1         
and t1.open_price =             
(select min(t.open_price)                
from stock_transaction_data_all t               
where t.date_ between                     
to_date(:1, ''yyyy-mm-dd'') and                     
to_date(:2, ''yyyy-mm-dd'')                 
and t.code_ = :3)         
and t1.date_ between to_date(:4, ''yyyy-mm-dd'') and             
to_date(:5, ''yyyy-mm-dd'')         
and t1.code_ = :6       
order by t1.date_ desc';
-- 表示某只股票在某个时间内，最低价出现的那一天
v_stock_low_date DATE;
-- 表示stock_name字段
v_stock_name VARCHAR2(100);
-- 表示board_name字段
v_board_name VARCHAR2(100); -- T_STOCK_TOP_RESULT类型对象。表示满足条件的股票
stock_top_result T_STOCK_TOP_RESULT;
-- 用于表示是否查询到了记录
num NUMBER;
-- 表示查询到了多少条记录
record_num NUMBER := 1;
BEGIN
  -- 初始化T_STOCK_TOP_RESULT_ARRAY类型的数组
  stock_top_result_array := T_STOCK_TOP_RESULT_ARRAY();
  OPEN cur_top_stock FOR vc_cur_top_stock USING p_begin_date,
  p_end_date;
  LOOP
    FETCH cur_top_stock bulk collect
    INTO top_stock_arr limit CN_BATCH_SIZE_CONSTANT;
    FOR i IN 1 .. top_stock_arr.count
    LOOP
      -- 如果已经到达了p_number个股票的数量，则直接退出存储过程
      IF record_num >= p_number THEN
        EXIT;
      END IF;
      v_stock_code := top_stock_arr(i).stock_code;
      -- 获取某只股票在某段时间内，最高价出现的那一天
      EXECUTE immediate vc_max_high_date INTO v_stock_high_date USING p_begin_date,
      p_end_date,
      v_stock_code,
      p_begin_date,
      p_end_date,
      v_stock_code;
      -- 获取某只股票在某段时间内，最低价出现的那一天
      EXECUTE immediate vc_min_low_date INTO v_stock_low_date USING p_begin_date,
      p_end_date,
      v_stock_code,
      p_begin_date,
      p_end_date,
      v_stock_code;
      -- 要求最高价出现的时间晚于最低价出现的时间。如果最高价出现的时间早于或等于最低价出现的时间，则放弃这只股票
      -- 要求不能有停牌的情况，否则放弃这只股票
      -- 要求不能有过多的一字涨停板的情况，否则放弃这只股票
      IF v_stock_high_date > v_stock_low_date AND fnc_stock_stop(v_stock_code, p_begin_date, p_end_date) = -1 AND fnc_stock_word_limit(v_stock_code, p_begin_date, p_end_date, p_word_limit_num) = -1 THEN
        -- 有时表STOCK_TRANSACTION_DATA_ALL中有记录，但表STOCK_INFO中没有记录，所以要先判断下numm是否为0
        EXECUTE immediate 'select count(t.name_) from STOCK_INFO t where t.code_ = upper(:1)' INTO num USING top_stock_arr(i).stock_code;
        IF num != 0 AND record_num <= p_number THEN
          EXECUTE immediate 'select t.name_ stock_name, b.name board_name from STOCK_INFO t                            
join board b on b.id=t.board_id                            
where t.code_ = upper(:1)' INTO v_stock_name,
          v_board_name USING top_stock_arr(i).stock_code;
          stock_top_result    := T_STOCK_TOP_RESULT(top_stock_arr(i) .stock_code, v_stock_name, v_board_name, top_stock_arr(i).rate);
          IF stock_top_result IS NOT NULL THEN
            stock_top_result_array.extend;
            stock_top_result_array(stock_top_result_array.count) := stock_top_result;
            record_num                                           := record_num + 1;
            UTL_FILE.PUT_LINE(file_handle, stock_top_result_array(stock_top_result_array.count) .get_stock_code() || '   ' || stock_top_result_array(stock_top_result_array.count) .get_stock_name() || '   ' || stock_top_result_array(stock_top_result_array.count) .get_board_name() || '   ' || stock_top_result_array(stock_top_result_array.count) .get_rate());
          END IF;
        END IF;
      END IF;
    END LOOP;
    EXIT
  WHEN top_stock_arr.count < CN_BATCH_SIZE_CONSTANT;
  END LOOP;
  CLOSE cur_top_stock;
END FIND_TOP_STOCK;
/*---------------------------------------------- 查找某段时间内跌幅最大的num只股票 ----------------------------------------*/
PROCEDURE FIND_LAST_STOCK(
    p_begin_date IN VARCHAR2,
    p_end_date   IN VARCHAR2,
    p_number     IN NUMBER,
    stock_last_result_array OUT T_STOCK_LAST_RESULT_ARRAY)
AS
  -- 表示stock_code字段
  v_stock_code VARCHAR2(100);
  -- 表示stock_name字段
  v_stock_name VARCHAR2(100);
  -- 表示board_name字段
  v_board_name VARCHAR2(100);
  -- 判断stock_name是否存在
  v_stock_name_exist NUMBER;
  -- 判断board_name是否存在
  v_board_name_exist NUMBER;
  -- 表示某只股票在某个时间内，最高价出现的那一天
  v_stock_high_date DATE;
  -- 表示某只股票在某个时间内，最低价出现的那一天
  v_stock_low_date DATE;
  -- T_STOCK_LAST_RESULT类型对象。表示满足条件的股票
  stock_last_result T_STOCK_LAST_RESULT;
  -- 查找beginDate至endDate中，按股票的跌幅进行排列，显示STOCK_CODE和跌幅百分比
  CURSOR cur_last_stock
  IS
    SELECT t.code_,
      ROUND((MAX(t.open_price) - MIN(t.close_price)) / MIN(t.open_price) * 100, 2) AS rate
    FROM stock_transaction_data t
    WHERE t.date_ BETWEEN to_date(p_begin_date, 'yyyy-mm-dd') AND to_date(p_end_date, 'yyyy-mm-dd')
    GROUP BY t.code_
    ORDER BY rate DESC;
  -- 查询某只股票在某段时间内，最高价出现的那一天。注意：可能这个价格出现的不止一天，因此只取最晚的一天
  CURSOR cur_max_high_date
  IS
    SELECT DISTINCT t1.date_
    FROM stock_transaction_data t1
    WHERE rownum     <= 1
    AND t1.open_price =
      (SELECT MAX(t.open_price)
      FROM stock_transaction_data t
      WHERE t.date_ BETWEEN to_date(p_begin_date, 'yyyy-mm-dd') AND to_date(p_end_date, 'yyyy-mm-dd')
      AND t.code_ = v_stock_code
      )
  AND t1.date_ BETWEEN to_date(p_begin_date, 'yyyy-mm-dd') AND to_date(p_end_date, 'yyyy-mm-dd')
  AND t1.code_ = v_stock_code
  ORDER BY t1.date_ DESC;
  -- 查询某只股票在某段时间内，最低价出现的那一天。注意：可能这个价格出现的不止一天，因此只取最早的一天
  CURSOR cur_min_low_date
  IS
    SELECT DISTINCT t1.date_
    FROM stock_transaction_data t1
    WHERE rownum      <= 1
    AND t1.close_price =
      (SELECT MIN(t.close_price)
      FROM stock_transaction_data t
      WHERE t.date_ BETWEEN to_date(p_begin_date, 'yyyy-mm-dd') AND to_date(p_end_date, 'yyyy-mm-dd')
      AND t.code_ = v_stock_code
      )
  AND t1.date_ BETWEEN to_date(p_begin_date, 'yyyy-mm-dd') AND to_date(p_end_date, 'yyyy-mm-dd')
  AND t1.code_ = v_stock_code
  ORDER BY t1.date_ ASC;
  -- 创建UTL_FILE.file_type对象，用于读写文件
  file_handle UTL_FILE.file_type;
BEGIN
  file_handle             := UTL_FILE.FOPEN('TXTDIR', 'FIND_LAST_STOCK.txt', 'w');
  stock_last_result_array := T_STOCK_LAST_RESULT_ARRAY();
  FOR i IN cur_last_stock
  LOOP
    v_stock_code := i.code_;
    -- 获取某只股票在某段时间内，最高价出现的那一天
    FOR j IN cur_max_high_date
    LOOP
      v_stock_high_date := j.date_;
    END LOOP;
    -- 获取某只股票在某段时间内，最低价出现的那一天
    FOR j IN cur_min_low_date
    LOOP
      v_stock_low_date := j.date_;
    END LOOP;
    -- 要求最高价出现的时间早于最低价出现的时间。如果最高价出现的时间晚于或等于最低价出现的时间，则放弃这只股票
    -- 当下跌幅度超过了11%，就表示之前有可能是除权
    IF v_stock_high_date < v_stock_low_date AND fnc_stock_xr(v_stock_code, p_begin_date, p_end_date) = -1 THEN
      /*dbms_output.put_line(i.code_ || '   ' || i.rate);*/
      -- 判断stock_name是否存在，如果存在则将其赋给v_stock_name变量
      SELECT COUNT(t.name_)
      INTO v_stock_name_exist
      FROM STOCK_INFO t
      WHERE t.code_          = upper(i.code_);
      IF v_stock_name_exist <> 0 THEN
        SELECT t.name_
        INTO v_stock_name
        FROM STOCK_INFO t
        WHERE t.code_ = upper(i.code_);
      END IF;
      -- 判断board_name是否存在，如果存在则将其赋给v_board_name变量
      SELECT COUNT(b.name)
      INTO v_board_name_exist
      FROM STOCK_INFO t
      JOIN board b
      ON b.id                = t.board_id
      WHERE t.code_          = upper(i.code_);
      IF v_board_name_exist <> 0 THEN
        SELECT b.name
        INTO v_board_name
        FROM STOCK_INFO t
        JOIN board b
        ON b.id       = t.board_id
        WHERE t.code_ = upper(i.code_);
      END IF;
      stock_last_result := T_STOCK_LAST_RESULT(i.code_, v_stock_name, v_board_name, i.rate);
      stock_last_result_array.extend;
      stock_last_result_array(stock_last_result_array.count) := stock_last_result;
      UTL_FILE.PUT_LINE(file_handle, stock_last_result_array(stock_last_result_array.count) .get_stock_code() || '   ' || stock_last_result_array(stock_last_result_array.count) .get_stock_name() || '   ' || stock_last_result_array(stock_last_result_array.count) .get_board_name() || '   -' || stock_last_result_array(stock_last_result_array.count) .get_rate());
    END IF;
  END LOOP;
END FIND_LAST_STOCK;
/*---------------------------------------------------- test -------------------------------------------------*/
PROCEDURE TEST(
    stockDate IN VARCHAR2)
AS
BEGIN
  ------------------------------------------ standard declare section ---------------------------------------------------
  DECLARE
    -- 表示stock_code
    stockCode VARCHAR2(10);
    -- 某一只股票某一日的记录
    singleStockOneRecord stock_moving_average%rowtype;
    -- define cursor section.返回全部stock_code
    CURSOR allStockCode
    IS
      SELECT DISTINCT t.stock_code
      FROM stock_moving_average t
      ORDER BY t.stock_code ASC;
    -- 某一只股票某一日（不包括这一日）之后所有日期的所有记录
    CURSOR singleStockRecord
    IS
      SELECT *
      FROM stock_moving_average t
      WHERE t.stock_code = stockCode
      AND t.stock_date   < to_date(stockDate, 'yyyy-mm-dd')
      AND rownum        <= 1
      ORDER BY t.stock_date DESC;
    ------------------------------------------ standard declare section ---------------------------------------------------
  BEGIN
    --------------------------------- standard for loop section --------------------------------------------------------
    FOR i IN allStockCode
    LOOP
      stockCode := i.stock_code;
      SELECT *
      INTO singleStockOneRecord
      FROM stock_moving_average t
      WHERE t.stock_code = stockCode
      AND t.stock_date   = to_date(stockDate, 'yyyy-mm-dd');
      FOR j IN singleStockRecord
      LOOP
        dbms_output.put_line(j.stock_code || '   11111' || singleStockOneRecord.today_up_down_percentage);
        dbms_output.put_line(j.stock_date || '   22222' || j.today_up_down_percentage);
        IF j.today_up_down_percentage <> 0 AND singleStockOneRecord.today_up_down_percentage / j.today_up_down_percentage > 2 THEN
          dbms_output.put_line(j.stock_code);
        END IF;
      END LOOP;
    END LOOP;
  END;
END TEST;
-- end procedure
PROCEDURE FIND_BOND_OPERATION_STOCK(
    p_date IN VARCHAR2,
    p_stock_result_array OUT t_stock_result_array)
AS
  -- 表示stock_code
  v_code VARCHAR2(50);
  -- 是否是第一条记录
  v_first BOOLEAN := true;
  -- 是否金叉
  v_kd_gold_cross BOOLEAN := false;
  -- ma250是否单调不递减
  v_ma250_not_decrease BOOLEAN := true;
  -- 收盘价是否在(mb+dn)/2之下
  v_close_price_down_mb_dn BOOLEAN := false;
  -- MACD是否金叉
  v_macd_gold_cross BOOLEAN := false;
  -- 表示T_STOCK_RESULT类型
  v_stock_result T_STOCK_RESULT;
  -- 某一只股票的第一条记录
  row_first_stda stock_transaction_data_all%rowtype;
  -- 某一只股票的上一条记录
  row_last_stda stock_transaction_data_all%rowtype;
  -- 某一只股票最近两周的记录的k属性
  v_last_first_stock_week_k  NUMBER;
  v_last_second_stock_week_k NUMBER;
  -- 上周KD的k是否大于等于上上周KD的k
  v_kd_increase BOOLEAN := false;
  -- 查询某一日的全部股票的代码
  CURSOR cur_stock_code
  IS
    SELECT t.code_
    FROM stock_transaction_data_all t
    WHERE t.date_ = to_date(p_date, 'yyyy-mm-dd');
  -- 查询某一个股票最近2个交易日的记录
  CURSOR cur_two_stda_desc
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data_all t
      WHERE t.code_ = v_code
      ORDER BY t.date_ DESC
      )
  WHERE rownum <= 2;
  -- 查询某一个股票最近20个交易日的记录
  CURSOR cur_20_stda_desc
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data_all t
      WHERE t.code_ = v_code
      ORDER BY t.date_ DESC
      )
  WHERE rownum <= 20;
  -- 查询某一个股票最近1个交易日的记录
  CURSOR cur_1_stda_desc
  IS
    SELECT *
    FROM
      (SELECT *
      FROM stock_transaction_data_all t
      WHERE t.code_ = v_code
      ORDER BY t.date_ DESC
      )
  WHERE rownum <= 1;
  -- 查询某一个股票最近2个交易周的记录
  CURSOR cur_last_two_stock_week_k
  IS
    SELECT *
    FROM
      (SELECT t.k
      FROM stock_week t
      WHERE t.code_  = v_code
      AND t.end_date < to_date(p_date, 'yyyy-mm-dd')
      ORDER BY t.end_date DESC
      )
  WHERE rownum <= 2;
BEGIN
  -- 初始化
  p_stock_result_array := T_STOCK_RESULT_ARRAY();
  FOR i IN cur_stock_code
  LOOP
    v_code                     := i.code_;
    v_first                    := true;
    v_kd_gold_cross            := false;
    v_ma250_not_decrease       := true;
    v_close_price_down_mb_dn   := false;
    v_last_first_stock_week_k  := NULL;
    v_last_second_stock_week_k := NULL;
    v_kd_increase              := false;
    -- 判断是否在当前交易日KD金叉
    FOR i IN cur_two_stda_desc
    LOOP
      -- 判断是否是第一条记录
      IF v_first        = true THEN
        row_first_stda := i;
        v_first        := false;
        CONTINUE;
      END IF;
      -- 如果是第二条记录判断KD是否在当前交易日金叉
      IF v_first = false THEN
        /*IF row_first_stda.k > row_first_stda.d AND i.k <= i.d and*/
        IF row_first_stda.k >= i.k AND (row_first_stda.k + row_first_stda.d) / 2 <= 50 THEN
          v_kd_gold_cross   := true;
        END IF;
      END IF;
      IF v_kd_gold_cross = false THEN
        EXIT;
      END IF;
    END LOOP;
    -- 判断250日均线是否单调不递减
    v_first := true;
    FOR i IN cur_20_stda_desc
    LOOP
      -- 判断是否是第一条记录
      IF v_first       = true THEN
        row_last_stda := i;
        v_first       := false;
        CONTINUE;
      END IF;
      -- 判断ma250是否单调不递减
      IF i.ma250      <= row_last_stda.ma250 AND i.ma20 <= row_last_stda.ma20 THEN
        row_last_stda := i;
        CONTINUE;
      ELSE
        v_ma250_not_decrease := false;
        EXIT;
      END IF;
    END LOOP;
    -- 判断MACD是否金叉
    /*FOR i IN cur_1_stda_desc LOOP
    IF i.dif >= i.dea THEN
    v_macd_gold_cross := true;
    else
    v_macd_gold_cross := false;
    end if;
    END LOOP;*/
    -- 判断收盘价是否在(mb+dn)/2之下
    --    FOR i IN cur_stda_desc
    --    LOOP
    --      IF i.close_price            < (i.mb + i.dn_) / 2 THEN
    --        v_close_price_down_mb_dn := true;
    --      END IF;
    --      v_first := false;
    --      EXIT;
    --    END LOOP;
    -- 判断最近两周的周线级别KD的K是否单调不递减
    FOR i IN cur_last_two_stock_week_k
    LOOP
      IF v_last_first_stock_week_k IS NULL THEN
        v_last_first_stock_week_k  := i.k;
      ELSE
        v_last_second_stock_week_k := i.k;
      END IF;
    END LOOP;
    IF v_last_first_stock_week_k IS NOT NULL AND v_last_second_stock_week_k IS NOT NULL AND v_last_first_stock_week_k >= v_last_second_stock_week_k THEN
      v_kd_increase              := true;
    END IF;
    -- 如果都符合条件，则作为返回值
    IF v_kd_gold_cross = true AND v_ma250_not_decrease = true AND v_kd_increase = true
      /*AND
      v_macd_gold_cross = true*/
      /*v_close_price_down_mb_dn = true*/
      THEN
      v_stock_result := T_STOCK_RESULT(v_code, to_date(p_date, 'yyyy-mm-dd'));
      p_stock_result_array.extend();
      p_stock_result_array(p_stock_result_array.count) := v_stock_result;
      dbms_output.put_line(v_stock_result.code_);
    END IF;
  END LOOP;
END FIND_BOND_OPERATION_STOCK;
PROCEDURE FIND_TURN_UP_STOCK(
    p_date IN VARCHAR2/*,
    p_stock_result_array OUT t_stock_result_array*/)
AS
  -- 表示stock_code
  v_code VARCHAR2(50);
  -- stock_transaction_data_all表类型的row变量
  row_stda stock_transaction_data_all%rowtype;
  -- 查询某一日的所有股票代码
  CURSOR cur_stock_code
  IS
    SELECT DISTINCT t.code_
    FROM stock_transaction_data_all t
    WHERE t.date_=to_date(p_date, 'yyyy-mm-dd');
  -- 查询某一只股票最近十天的记录
  CURSOR cur_top_stock_detail
  IS
    SELECT *
    FROM mdl_top_stock_detail t
    WHERE t.code_               =v_code
    AND t.date_                 =to_date(p_date,'yyyy-mm-dd')
    AND t.up_down_percentage_21>=80;
  CURSOR cur_stda
  IS
    SELECT *
    FROM
      (SELECT *
      FROM
        (SELECT *
        FROM stock_transaction_data_all t
        WHERE t.code_=v_code
        AND t.date_ <=to_date(p_date,'yyyy-mm-dd')
        ORDER BY t.date_ DESC
        )
    WHERE rownum<=21
      ) t1
    WHERE t1.change_range<0;
  BEGIN
    FOR i IN cur_stock_code
    LOOP
      v_code := i.code_;
      FOR j IN cur_top_stock_detail
      LOOP
        BEGIN
          OPEN cur_stda;
          LOOP
            FETCH cur_stda INTO row_stda;
            EXIT
          WHEN cur_stda%NOTFOUND;
            dbms_output.put_line(v_code);
          END LOOP;
          CLOSE cur_stda;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
          continue;
        END;
      END LOOP;
    END LOOP;
  END FIND_TURN_UP_STOCK;
END pkg_stock_transaction_data;